Partitioning The Part is Better than the Whole

1
Partitioning The Part is Better than the Whole
www.ntirety.com
LAOUG Thursday, February 21, 2008
Michael S. Abbey Database Officer Oracle
Practice
2
Agenda

• Partitioning advantages
• Partitioning existing objects
• Naming conventions
• What's new with 10g
• Partitioned indexesa global problem
• Tips and Tricks

3
Advantages

• Large objects can be treated as a collection of
  small objects
• multi-million row tables are impossible to manage
• statistic collection using partnamegt feature
• smaller logical objects influence execution plans
• Housekeeping done now rather than later
• Pro-active rather than re-active
• Partition-wise joins

4
Advantages

• Partition pruning
• queries that use partition key columns in
  predicate
• treats a huge partitioned table as a handful of
  smaller counterparts
• DBMS_STATS
• global vs. partition-level
• decisions made a run time
• Partition maintenance need not affect whole table

5
Planning

• Candidates
• Multi-million row tables
• Multi-gigabyte or terabyte tables
• Fat tables with hundreds of thousands of rows
• Should all the data be partitioned
• Opportunity for archival at the same time
• Selection of a partition key
• Be prepared

6
Planning

• Range is most common and lends itself the best to
  archival
• List available in 9 and 10, adds some complexity
  to partition maintenance
• Hash is the best for equal row distribution and
  requires the least maintenance requirements
• Composite is a mixture of range and hash / list
  and hash

7
Partitioning Existing Objects

• Excuses
• I don't have EE (show stopper!)
• I don't know how ... September 24, 2007
• I cannot afford the hours of down time ... not
  necessary using materialized views
• I don't have the rollback or disk space for all
  the redo
• Big objects are a headache ... and they are only
  getting bigger

8
Choosing a partition key

• Way the data is retrieved by your applications
• Way existing unique indexes are setup
• partition key column(s) must exist in a
  partitioned local unique index
• table partitioned on id
• id must exist in unique index (using index with
  PK)
• global unique indexes must be prefixed
• table partitioned on column1
• global unique index must start with column1
• Try to use a single column whenever possible

9
Choosing a partition key

• Columns cannot be modified once the partitioning
  is done
• Expand the size of number and varchar2 columns
  prior to partitioning
• Archival possibilities/requirements affect choice
• Impact of a multi-column partition key
• Unique index syntax requirements

Invest the time now
10
Partition boundaries
Choice of boundaries for partitions when
partitioning an existing table numeric partition
key.
Goal

• Use imbedded functionality of optimizer
  histograms
• Table has 35,689 rows
• Partitioning key column is number
• Target is equal row distribution

11
Partition boundaries
SQLgt begin 2 dbms_stats.gather_table_stats
(ownnamegt'OPSORACLE', 3
tabnamegt'SALE', 4
estimate_percentgt10, 5
method_optgt'for all indexed columns size 8')
6 end 7 / PL/SQL procedure successfully
completed.

• estimate_percent studies have shown small values
  are desirable
• value of buckets may need tweaking to achieve goal

12
Partition boundaries
SQLgt select endpoint_value from
user_tab_histograms 2 where table_name
'SALE' ENDPOINT_VALUE --------------
10007 598856809 598861376
598866106 598870484 758155561
2103623867 3577981994 4998662814 9 rows
selected.
13
Partitioned counterpart
SQLgt create table sale_p ( 2 id
number, 3 cust_id number, 4 quantity
number) 5 partition by range (id) 6
(partition sale_p001 values less than
(598856809), 7 partition sale_p002 values
less than (598861376), 8 partition sale_p003
values less than (598866106), 9 partition
sale_p004 values less than (598870484), 10
partition sale_p005 values less than
(758155561), 11 partition sale_p006 values
less than (2103623867), 12 partition sale_p007
values less than (3577981994), 13 partition
sale_p008 values less than (4998662814) 14
partition sale_p009 values less than
(5300000000)) Table created. SQLgt SQLgt alter
table sale_p add constraint sale_p_pk 2
primary key (id) using index local Table
altered.
14
The materialized view
SQLgt create snapshot sale_p on prebuilt table 2
refresh fast start with sysdate 3 next
sysdate1/24 4 as select from
sale Materialized view created. SQLgt begin 2
dbms_snapshot.refresh('SALE_P','C') 3 end
4 / PL/SQL procedure successfully completed.
15
Sanity check
SQLgt select count() from sale
COUNT() ---------- 35689 1 row
selected. SQLgt select count() from sale_p
COUNT() ---------- 35689 1 row selected.
16
Additional indexes

• to match SALE non-partitioned
• global index on CUST_ID
• non-partitioned index on QUANTITY
• building immediately
• saves time during cutover
• renaming code can be pre-created
• run DBMS_STATS now
• consistency and completeness check at your
  leisure (e.g., column defaults and check
  constraints)

17
Additional indexes
SQLgt create index sale_p_n1 on sale_p (cust_id)
2 global partition by range (cust_id) 3
(partition sale_p_n1_p001 values less than (40),
4 partition sale_p_n1_p002 values less than
(90), 5 partition sale_p_n1_p003 values less
than (600), 6 partition sale_p_n1_pmax values
less than (maxvalue)) Index created. SQLgt
create index sale_p_n2 on sale_p
(quantity) Index created.
18
The cutover

• negotiate an acceptable time
• quiesce applications
• drop snapshot on prebuilt table
• drop snapshot log on SALE
• drop indexes on SALE
• norows export on SALE
• rename SALE and SALE_P
• import into partitioned SALE

19
Snapshot work
SQLgt drop snapshot sale_p Materialized view
dropped. SQLgt drop snapshot log on
sale Materialized view log dropped. Table
altered.
20
Drop indexes on SALE
SQLgt drop index sale_n1 Index dropped. SQLgt
drop index sale_n2 Index dropped. SQLgt alter
table sale drop constraint sale_pk Table
altered.
21
No rows export
/oraclegt exp userid/ rowsn tablessale
filesale statisticsnone Export Release
9.2.0.7.0 - Production on Wed Feb 30 094822
2009 ... ... Note table data (rows) will not be
exported ... ... About to export specified tables
via Conventional Path ... . . exporting table
SALE Export terminated
successfully with warnings.
22
Important Points

• expand partition key column
• alter table sale modify id number(8)
• manual complete refresh required to kickstart the
  snapshot job
• secondary index creation time must be factored
  into the cutover if not created with snapshot
• must (should) use DBMS_STATS rather than ANALYZE

23
Naming Conventions

• 30 characters
• self-explanatory
• formulation is great gt adoption is better
• data dictionary familiarity
• user_tabind_subpartitions
• user_part_tablesindexes
• PARTITION_POSITION is the most important column

24
Naming conventions worst enemy
Partition maintenance ...
25
Naming ConventionsTables

• table name
• partition number
• appropriate left zero padding so all partition
  numbers are the same length
• overestimate in case many new partitions required
• partition or subpartition
• separated by underscores
• creative abbreviation approach
• identify hash partitions with HP

26
SALE
SQLgt alter table sale split partition sale_pmax
at (90) Table altered. SQLgt select
partition_name from user_tab_partitions PARTITIO
N_NAME ------------------------------ SYS_P1 SYS_P
2 SALE_P001 SALE_P002 SALE_P003 SALE_P004 SALE_P00
5 SALE_P006 SALE_P007 9 rows selected.
27
Fixing partition names
SQLgt select 'alter table sale rename partition
' 2 partition_name' to sale_p'
3 lpad(partition_position,3,'0')'' 4
from user_tab_partitions 5 where
table_name 'SALE' alter table sale rename
partition SYS_P1 to sale_p008 alter table sale
rename partition SYS_P2 to sale_p009 alter table
sale rename partition SALE_P001 to
sale_p001 alter table sale rename partition
SALE_P002 to sale_p002 alter table sale rename
partition SALE_P003 to sale_p003 alter table
sale rename partition SALE_P004 to
sale_p004 alter table sale rename partition
SALE_P005 to sale_p005 alter table sale rename
partition SALE_P006 to sale_p006 alter table
sale rename partition SALE_P007 to sale_p007
28
Fixing partition names
SQLgt alter table sale rename partition SALE_P007
to sale_p007 alter table sale rename partition
SALE_P007 to sale_p007
ERROR at line 1 ORA-14081
new partition name must differ from the old
partition name SQLgt select 'alter table
sale rename partition ' 2
partition_name' to sale_p' 3
lpad(partition_position,3,'0')'' 4 from
user_tab_partitions 5 where table_name
'SALE' 6 and partition_name not like
'SALE_P' alter table sale rename partition
SYS_P1 to sale_p008 alter table sale rename
partition SYS_P2 to sale_p009
29
Fixing partition names
SQLgt select 'alter table sale rename partition
' 2 partition_name' to sale_pmax'
3 from user_tab_partitions 4 where
table_name 'SALE' 5 and
partition_position 6 (select
max(partition_position) 7 from
user_tab_partitions 8 where table_name
'SALE') alter table sale rename partition
SALE_P009 to sale_pmax

• do not QUIT until naming convention is 100
  followed
• there is almost always a better way to do it

30
Fixing partition names
SQLgt alter table sale split partition sale_pmax
at (90) into 2 (partition sale_p008,partition
sale_pmax) Table altered. SQLgt select
partition_name 2 from user_tab_partitions 3
where table_name 'SALE' 4 order by
partition_position SALE_P001 SALE_P002 SALE_P003
SALE_P004 SALE_P005 SALE_P006 SALE_P007 SALE_P008
SALE_PMAX
31
SALE
SQLgt alter table sale add partition
sale_p20060331 2 values less than 3
(to_date('01-APR-2006','DD-MON-YYYY')) Table
altered. SQLgt select partition_name from
user_tab_partitions 2 where table_name
'SALE' SALE_P20040331 SALE_P20040630 SALE_P200409
30 SALE_P20041231 SALE_P20050331 SALE_P20050630 SA
LE_P20050930 SALE_P20051231 SALE_P20060331
32
SALE
create table sale (id date, ...
varchar2(32)) partition by hash (id) partitions 4
33
Fixing partition names
SQLgt select 'alter table sale rename partition
' 2 partition_name' to sale_hp'
3 lpad(partition_position,3,'0')'' 4
from user_tab_partitions 5 where
table_name 'SALE' alter table sale rename
partition SYS_P212 to sale_hp001 alter table
sale rename partition SYS_P213 to
sale_hp002 alter table sale rename partition
SYS_P214 to sale_hp003 alter table sale rename
partition SYS_P215 to sale_hp004
34
Adding to SALE
SQLgt alter table sale add partition Table
altered. SQLgt alter table sale add
partition Table altered. SQLgt alter table sale
add partition Table altered. SQLgt alter table
sale add partition Table altered.
SQLgt select partition_name 2 from
user_tab_partitions 3 where table_name
'SALE' SYS_P7 SYS_P8 SYS_P3 SYS_P4 SYS_P5 SYS_P6
SYS_P9 SYS_P10
35
Fixing partition names
SQLgt select 'alter table sale rename partition
' 2 partition_name' to sale_hp'
3 lpad(partition_position,3,'0')'' 4
from user_tab_partitions 5 where
table_name 'SALE' alter table sale rename
partition SYS_P7 to sale_hp005 alter table sale
rename partition SYS_P8 to sale_hp006 alter
table sale rename partition SYS_P3 to
sale_hp001 alter table sale rename partition
SYS_P4 to sale_hp002 alter table sale rename
partition SYS_P5 to sale_hp003 alter table sale
rename partition SYS_P6 to sale_hp004 alter
table sale rename partition SYS_P9 to
sale_hp007 alter table sale rename partition
SYS_P10 to sale_hp008
36
Naming ConventionsIndexes

• Can prove more difficult to stay in the 30
  character limit
• 3 part nomenclature
• Table name (e.g., SALE_)
• Index type and sequence
• n1 n2 n3
• u1 u2 u3
• f1 f2 f3
• Partition number
• Creative abbreviation required

37
Naming ConventionsIndexes
range list
hash
38
Naming ConventionsIndexes

• SALE_N2_P001_HSP004
• SALE_F1_P002_SP008
• SALE_N5_HP002
• SALE_U3_P003_L003

39
Whats new in 10g
40
What's New in 11g
41
What's New in 11g
http//www.oracle.com/solutions/business_intellige
nce/partitioning.html
42
Indexes
Goal
Understand ramifications of partition splitting.

• invalid indexes and unusable index partitions is
  the nature of the beast if not local
• only way to add to a table that uses maxvalue
• keep ahead of yourself allowing no less than 2
  empty partitions before last
• can avoid invalidating index components
• Empty partitions valid and usable indexes

43
Splitting pmax
SQLgt alter table sale split partition sale_pmax
at (9101000000) 2 into (partition sale_p010,
partition sale_pmax) Table altered. SQLgt SQLgt
select partition_name,index_name,status 2
from user_ind_partitions PARTITION_NAME
INDEX_NAME STATUS ---------------- -----------
-------- SALE_P010 SALE_PK
UNUSABLE SALE_PMAX SALE_PK
UNUSABLE SALE_P001 SALE_PK
USABLE ... ... 11 rows selected.
Must be rebuilt
44
Splitting pmax
SQLgt select from sale partition (sale_pmax)
ID CUST_ID QUANTITY ----------
---------- ---------- 9101000000 12
31 9101010000 12 31 SQLgt
select from sale partition (sale_p010)
ID CUST_ID QUANTITY ---------- ----------
---------- 9100000000 12 31
45
Splitting affect

• If there are rows in the 2 partitions after the
  split
• global index partitions unusable
• non-partitioned indexes invalid
• local index partitions involved in the split
  unusable, others still fine
• No rows in 1 or both partitions should not
  invalidate any indexes or index partitions
• USER_INDEXES, USER_IND_PARTITIONS,
  USER_IND_SUBPARTITIONS

46
Invalid/unusable
47
Best practice
SQLgt select count() from sale partition
(sale_p001) 4599 SQLgt select count()
from sale partition (sale_p002) 4567 SQLgt
select count() from sale partition (sale_p003)
4730 SQLgt alter table sale drop partition
sale_p001 Table altered.
48
Best practice
SQLgt select partition_name,status from 2
user_ind_partitions order by 1 SALE_P002
USABLE SALE_P003
USABLE SALE_P004
USABLE SALE_P005
USABLE SALE_P006
USABLE SALE_P007
USABLE SALE_P008
USABLE SALE_P009
USABLE SALE_P010A
USABLE SALE_P010B
USABLE SALE_PMAX USABLE 11
rows selected. SQLgt select index_name,status
from user_indexes SALE_N1
UNUSABLE SALE_PK N/A
49
Best practice
SQLgt alter index sale_n1 rebuild Index
altered. SQLgt alter table sale truncate
partition sale_p002 Table truncated. SQLgt
alter table sale drop partition sale_p002 Table
altered.
50
Best practice
SQLgt select index_name,status from
user_indexes SALE_N1
UNUSABLE SALE_PK N/A SQLgt
select partition_name,status from
user_ind_partitions 2 order by 1 SALE_P003
USABLE SALE_P004
USABLE SALE_P005
USABLE SALE_P006
USABLE SALE_P007
USABLE SALE_P008
USABLE SALE_P009
USABLE SALE_P010A
USABLE SALE_P010B
USABLE SALE_PMAX USABLE 10
rows selected.
51
Best practice
SQLgt alter index sale_n1 rebuild Index
altered. SQLgt delete sale partition
(sale_p003) 4730 rows deleted. SQLgt alter
table sale drop partition sale_p003 Table
altered. SQLgt select index_name,status from
user_indexes SALE_N1
VALID SALE_PK N/A
52
Best practice
SQLgt select partition_name,status 2 from
user_ind_partitions 3 order by 1 SALE_P004
USABLE SALE_P005
USABLE SALE_P006
USABLE SALE_P007
USABLE SALE_P008
USABLE SALE_P009
USABLE SALE_P010A
USABLE SALE_P010B
USABLE SALE_PMAX USABLE 9
rows selected.
53
Hash partitioned indexes

• Always invalidated by maintenance activities
• Partitions assume system-generated names at
  creation time

SQLgt alter table sale add constraint sale_pk
primary key 2 (id) using index local Table
altered. SQLgt SQLgt select partition_name,index_n
ame from user_ind_partitions SYS_P53
SALE_PK SYS_P54
SALE_PK SYS_P55
SALE_PK SYS_P56 SALE_PK 4
rows selected.
54
Fix hash partition index names
SQLgt select 'alter index 'index_name' rename
partition ' 2 partition_name' to
'index_name'_HP' 3
lpad(partition_position,3,'0') 4 from
user_ind_partitions alter index SALE_PK rename
partition SYS_P53 to SALE_PK_HP001 alter index
SALE_PK rename partition SYS_P54 to
SALE_PK_HP002 alter index SALE_PK rename
partition SYS_P55 to SALE_PK_HP003 alter index
SALE_PK rename partition SYS_P56 to SALE_PK_HP004
55
Add hash partitions
SQLgt alter table sale add partition Table
altered. SQLgt alter table sale add
partition Table altered. SQLgt alter table sale
add partition Table altered. SQLgt alter table
sale add partition Table altered.
56
Table partition names
SQLgt select 'alter table sale rename partition
'partition_name 2 ' to sale_hp'lpad(part
ition_position,3,'0') 3 from
user_tab_partitions alter table sale rename
partition SYS_P85 to sale_hp005 alter table sale
rename partition SYS_P86 to sale_hp006 alter
table sale rename partition SYS_P81 to
sale_hp001 alter table sale rename partition
SYS_P82 to sale_hp002 alter table sale rename
partition SYS_P83 to sale_hp003 alter table sale
rename partition SYS_P84 to sale_hp004 alter
table sale rename partition SYS_P87 to
sale_hp007 alter table sale rename partition
SYS_P88 to sale_hp008 8 rows selected.
57
Index partition names
SQLgt select 'alter index 'index_name' rename
partition ' 2 partition_name' to
'index_name'_hp' 3 lpad(partition_positio
n,3,'0') 4 from user_ind_partitions alter
index SALE_PK rename partition SYS_P81 to
SALE_PK_hp001 alter index SALE_PK rename
partition SYS_P85 to SALE_PK_hp005 alter index
SALE_PK rename partition SYS_P86 to
SALE_PK_hp006 alter index SALE_PK rename
partition SYS_P87 to SALE_PK_hp007 alter index
SALE_PK rename partition SYS_P82 to
SALE_PK_hp002 alter index SALE_PK rename
partition SYS_P83 to SALE_PK_hp003 alter index
SALE_PK rename partition SYS_P84 to
SALE_PK_hp004 alter index SALE_PK rename
partition SYS_P88 to SALE_PK_hp008 8 rows
selected.
58
Tips and Tricks Summary

• Make partition names self explanatory
• Use materialized views with fast refresh for
  close to zero downtime partitioning of existing
  tables
• Let export (no rows) or data pump (metadata_only)
  complete the cycle
• Keep last few partitions of range-based tables
  emptymonitor and report their contents regularly

59
(No Transcript)
60
Contact information
michael.abbey_at_ntirety.com
fenderpbs