Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

migrated from Feb 2 '12 at 0:48

This question came from our site for professional and enthusiast programmers.

When you tested it which one performed better? – Conrad Frix Feb 1 '12 at 18:30
@Jeremy - Do you mean that the table is partitioned by CATEGORY and subpartitioned by SUBCATEGORY? Or do you mean that the table is not subpartitioned at all and the partitioning key is a composite of CATEGORY and SUBCATEGORY? – Justin Cave Feb 1 '12 at 18:32
what is the actual partitioning scheme (simple example DDL would be nice)...list partitioning, using subpartitions, etc? Have u tried testing a simple example in a test env? – tbone Feb 1 '12 at 18:35
Yet another question migrated to DBA for no good reason. – Jon Heller Feb 2 '12 at 3:43

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.

share|improve this answer
In this particular case you need to filter on both. But in the genral case this is not always true. Filtering on an additional column can make the query slower by making an otherwise optimal index infeasible. – usr Feb 1 '12 at 21:19
@usr - But we're not talking about using an index. We're talking about partitioning. They're very different animals. Filtering just on a subpartition value is always going to be radically less efficient than filtering on the partition value and the subpartition value. If the question was related to an index on one or both values, you'd be spot on. – Justin Cave Feb 1 '12 at 21:27
Sure. I did not mean to disagree. I tried to amend your totally correct answer. – usr Feb 1 '12 at 21:29

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.