Ask the Expert

How to use partition pruning

In one of our partitioning tables, we have 7+ million records, broken down by monthly partitions partitioned on a time_seq numeric column. This time_seq points to a time_dmsn table that has the reporting_date as a primary key lead column, composite key. A query goes against the fact table using the time_seq with a reporting_date specified in the multitable join, not the time_seq. Depending on how many tables are joined, partition elimination does not occur. I have read places where partition elimination will only work if the where clause of the query contains the column that is partitioned. Is there any way to work around this type of situation? It seems to go against a star schema type of design.

    Requires Free Membership to View

One has to be very careful when they implement partitioning. In order for partition pruning to be used, which is one of the big benefits to partitioning, the database has to know what data is in what partitions. If your partition one COLUMN_A, but as asking for data from COLUMN_B in your WHERE clause, then there is a chance that the rows will be in all partitions! So the database cannot initially prune partitions from the result set. This is why the column you partition on is a very, very important decision. The partition column should be the one that is used most frequently in the WHERE clause. If your table was partitioned differently, then you will not be able to fully utilize partition pruning.

One of the solutions to this type of problem is to use partition Global Indexes. With partitioned Global indexes, your index can be partitioned, but the partitioning scheme is different than the index's table. This way, the system can prune index partitions and then use the index pointers to the interested rows. You might want to take a look at implementing Global partitioned indexes.

This was first published in May 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: