1

My question is similar to this one, but with a small difference. I have a query running on a single table with multiple WHERE conditions.

Assuming my table has multiple columns (col1 - col9) and I have a query like:

SELECT
    col1
    , col5
FROM table1
WHERE col1 = 'a'
    AND col2 = 'b'
    AND col3 = 100
    AND col4 = '10a'
    AND col5 = 1

And my indexes are:

  • col1 - unique / non-partitioned
  • col2, col3 - non-unique / partitioned
  • col4, col5 - non-unique / partitioned

My question is, if I'm using columns in my WHERE clause that cover multiple indexes, will (should?) the query pick the unique index first to generate a result set and then on that result set use the other two indexes for further filtering, sequentially reducing the result set?

Or will each index go over the entire data in the table and each condition will use an index and later merge all of the result sets?

(I don't have access to a table/data, this is more theoretical than practical).

Thank you in advance for any help

Community
  • 1
  • 1
Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
  • 3
    Not sure why it's different to the previous question as that was for one table too. Which index(es) Oracle uses depends on the selectivity of each - or what the CBO thinks the selectivity is, based on the statistics - among other things. Read about the query optimiser and how it makes decisions; [index joins](http://docs.oracle.com/cd/E29597_01/server.1111/e16638/optimops.htm#i56068) might be something you're interested in. (And the [index hint descriptions](http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#BABEFDFC) may explain what's happening a bit more too.) – Alex Poole Nov 03 '15 at 09:41
  • @AlexPoole Thank you for your input! – Radu Gheorghiu Nov 03 '15 at 09:44
  • 2
    Since index on `col1` is a unique index and you filter on `col1 = 'a'` it is quite obvious that Oracle will use only this one. Anything else would be a waste of resources. – Wernfried Domscheit Nov 03 '15 at 10:10

1 Answers1

3

The Oracle optimiser (in more recent versions of Oracle, and unless you force it to behave otherwise) is cost based rather than rule based. When the query is first executed it will consider many different paths to obtain the answer, and choose the one with the lowest cost.

So it's generally impossible to say, ahead of time, how the database will choose to answer a particular query. The answer is always - it depends. It depends on

  • The statistics for the table, and the number of distinct values on each column
  • The version of the database you are using
  • System and session parameters
  • Statistics for the index

In general, what it will do in most cases is to choose whatever is the most selective index. So if you only had one or two rows where col1='a', it would probably go in on that index, and then scan the rows within it.

As the other answer mentions, the database can combine B-Tree indexes by going through a bitmap conversion stage. This is relatively expensive, and not available in all Oracle versions, but it can happen .

So in summary, the database can do either of the approaches you mention. The only way to know what it will do in your circumstance is to use explain plan or the equivalent tools to watch what it does

James
  • 3,252
  • 1
  • 18
  • 33