Mohamed Houri’s Oracle Notes

February 8, 2007

Using Oracle Partitioning (I)

Filed under: Partitioning — hourim @ 12:13 pm

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:


Name             Type
 --------------- ---------
 Ide               NUMBER
 Dat_Part          DATE
 Nmfi_Ide          NUMBER
 Ptfl_ide          NUMBER

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:

select mast.*
 from mho_arc_mast mast
 where mast.DAT_PART = SYSDATE;

Execution Plan
 ----------------------------------------------------------
 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:

</pre>
select mast.*

from mho_arc_mast mast
 where mast.nmfi_ide = 35689;

generates this kind of explain plan

Execution 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.

2 Comments »

  1. If the partition key is a subset of the primary key & if the WHERE clause (in a query) has all of the fields of the primary key (including partition key), how will the optimiser react ?
    Will it do partition pruning based on the partition keys ?

    Comment by Rajeev — June 2, 2007 @ 8:26 pm | Reply

  2. Hi Rajeev,

    Yes it will do. When the partition key is in the where clause, without regards to the presence of an index or not, then the partition pruning will take place.

    There is a very nice discussion (where I have participated in) here below

    https://forums.oracle.com/forums/thread.jspa?threadID=2150455&start=0&tstart=0

    it is worth reading it

    Best Regards

    Comment by hourim — September 16, 2011 @ 7:11 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: