The Partitioning aspect brought by Oracle since its release 8i has been considered as a major improvement by the oracle data base developers and Administrators.
However, partitioning can make a system run many orders of magnitude slower when it is inappropriately applied. When a SQL statement accesses one or more partitioned tables, the Oracle optimizer attempts to use the information in the WHERE clause to eliminate some of the partitions from consideration during statement execution. This process, called partition pruning, speeds statement execution by ignoring partitions that cannot satisfy the statement’s WHERE clause. To do so, the optimizer uses information from the table definition with information from the statement’s WHERE clause.
In order for the oracle optimizer to make this type of decision, the WHERE clause must reference at least one column from the set of columns that comprise the partition key.
Hence, the partition type (range, hash, list or hash-range) design together with the partition key choice is the most important issue when you decide to partition your tables.
I am not going here to explain those different types of partitions largely documented in several excellent books as Practical Oracle 8i by Jonathan Lewis and Expert Oracle Data Base Architecture by Thomas Kyte. What I want to emphasize here is how to best partition, how to best choose the key partition and how to index using locally or globally partitioned index.
I would like to work by example. Let’s create a simple table called Mho_Arc_Mast described as:
I would like to range partition this table using the DAT_PART column as the key partition.
I would like also to create 16 partitions for DAT_PART on a quarter time base:
P_MHO_20040331 for DAT_PART less than 2004-04-01
P_MHO_20040631 for DAT_PART less than 2004-07-01
P_MHO_20081231 for DAT_PART less than 2009-01-01
So, what have conducted me to choose this column as the key partition? This is the most fundamental question every one should ask when designing a table partition.
Do the DAT_PART means any thing in my business? Or will my application be using many queries involving WHERE CLAUSES such as:
WHERE DAT_PART = SYSDATE
If answers to the above question are YES then you got a nice candidate to the key partition column. However if answers are NO then maintaining this column as a key partition will be at least not useful and in the worst case disastrous.
Let’s considers the YES and the NO cases in order to see the differences between them. Consider, the YES case and examine the following select and its corresponding explain plan:
from mho_arc_mast mast
where mast.DAT_PART = SYSDATE;
SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=2835 Bytes= 68040)
1 0 PARTITION RANGE (SINGLE)
2 1 TABLE ACCESS (FULL) OF 'MHO_ARC_MAST'(Cost=11 Card=2835 Bytes= 68040)
MHO_ARC_MAST table is range partitioned by DAT_PART. And there is no yet any index applied to it. This table contains 283467 rows.
You can point out that the optimizer is doing a single partition range.
In the NO case where the key DAT_PART do not figure in any where clause, the following select:
from mho_arc_mast mast
where mast.nmfi_ide = 35689;
generates this kind of explain plan
SELECT STATEMENT Optimizer=CHOOSE (Cost=157 Card=100 Bytes=2400)
1 0 PARTITION RANGE (ALL)
2 1 TABLE ACCESS (FULL) OF 'MHO_ARC_MAST' (Cost=157 Card=100 Bytes=2400)
where the oracle optimiser chooses to do a Partition Range All, starting from partition 1 (Pstart 1) to partition 16 (Pstop 16).
When the key partition is used in the where query clauses then a partition pruning (partition elimination) will occur improving performance. When the key partition is not used in the where query clauses, then selects not using the key partition are candidate to a full scan all partitions.