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 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.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.