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