simple question about oracle indexes

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...d开发者_运维技巧oes 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?

---

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 :)