simple question about oracle indexes

Question

If I have an oracle query like below:

SELECT * FROM table_a where A = "1", B = "2", C = "3"

for this query to pickup one of the indexes of table_a...does the index need to be on all 3 of these columns?

What I am asking is:

1. What if Index is on A, B, C, D?

2. What if Index is on B, C?

3. Will the index only be picked when it is on A, B, C?

Answer 1

Indexing in Oracle is a very complex subject, but to answer your most immediate question: no, the index does not need to be on all 3 columns, nor does it have to be exactly on those 3 columns.

For example, if you had a single-column index for column A, the index could still (possibly) be used during the execution of your query.

However, note that what really is going to happen is dependent on the data you have in the table, the size of that data, distribution of values, statistics and whether they are up-to-date, etc. Depending on the case, Oracle might still determine that doing a full table scan will be more effective, even if applicable indexes exist.

Answer 2

The Oracle Cost Based Optimizer (CBO) tries to choose the cheapest access path to the table. Access paths to a single table include index range scans, index full scans and table full scans; the CBO will estimate the cost of each of these plans and will choose the plan with the lowest cost.

1. What if Index is on A, B, C, D?

yes, Oracle might use this index - and the cost might be quite low because the leading columns (3 of them) of the index are constrained in your query.

2. What if Index is on B, C?

yes, Oracle might use this index - and the cost might be quite low because all of the columns of the index are constrained in your query.

3. Will the index only be picked when it is on A, B, C?

no, it is not exclusive. yes, Oracle might use this index - and the cost might be quite low because all the columns of the index are constrained in your query.

Other factors to consider:

• your query selects * (all columns) from the table. IF the table only has four columns (A, B, C, D), the CBO will probably prefer a plan that satisfies the query entirely from the index on (A, B, C, D) without accessing the table at all.
• you didn't ask the more interesting question: "What if the index is on D, C, B, A?" - the answer to which is that yes, Oracle might possibly use the index (e.g. with a index full scan or an index skip scan). Just thought I'd throw that in there :)