Database partition efficiency

Question

Let's assume that you have a table that partitions on CATEGORY and SUBCATEGORY. Also assume that every subcategory can be mapped to a category, i.e. subcategory are proper subsets of categories.

Let's also say that for my query, I know I am only interested in a subcategory of 'A', and that I know that everything with a subcategory of A has a category of A.

Which of these should I go with this:

Select blah from blahblah where subcategory = 'A'

Select blah from blahblah where subcategory = 'A' and category = 'A';

Which is to say, is the second faster?

Answer 1

Assuming that you mean that the table is partitioned on CATEGORY and subpartitioned on SUBCATEGORY, the query that includes the CATEGORY predicate will be much more efficient. Since Oracle cannot know that subcategory A only exists where the category is A, it would generally not be able to eliminate any partitions. Instead, it would have to search the subpartition of each partition where a SUBCATEGORY of A would be stored in order to fulfill the query (assuming that there is no global index that could be used). If you include both predicates, Oracle should be able to eliminate all but the partition where CATEGORY A values are stored and then search only the subpartition where SUBCATEGORY A values are stored.