CODEWITHSUNDEEP
oracle-databaseindexing
Cal
Cal

Reputation: 867

Oracle is using a one-column index although other indices available

We need to query an 8-million-row table constantly so we decided to create an index for the job.

In the query we use 3 columns in the where condition, it's like

SELECT something 
FROM my_table 
WHERE TRUNC(DATE) = TRUNC(SYSDATE) 
  AND IS_GREETED = 1 
 AND EMP_ID = 'JOHN.SMITH'

So we created index one using EMP_ID only and index two using all these 3 fields (DATE, IS_GREETED, EMP_ID).

With SQL Developer's autotrace function we found out that Oracle is actually using the index one, instead of index two, contrary to what we had thought.

What is the reason for this? Are there other ways we can improve performance? Thanks.

Upvotes: 0

Views: 90

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132680

Oracle doesn't generally use an index on <column> for a WHERE clause that references function(<column>).

You could try creating an index on (TRUNC(DATE), IS_GREETED, EMP_ID)

Upvotes: 5

Alex Poole
Alex Poole

Reputation: 191465

Oracle won't consider the DATE field for the index selection (generally, anyway, not sure if there are exceptions) because you've got a function around it, TRUNC in this case. This is more likely to choose the index you already have for a range scan:

SELECT something 
FROM my_table 
WHERE DATE >= TRUNC(SYSDATE) and DATE < (TRUNC(SYSDATE) + INTERVAL '1' DAY)
AND IS_GREETED = 1 
AND EMP_ID = 'JOHN.SMITH'

... or use a function-based index as Tony showed (not sure how I didn't see that before answering).

Depending on how selective the fields are, you might find the index performs better with the columns in a different order, e.g. EMP_ID, IS_GREETED, DATE. You'll need to look at the plan and experiment to see what gives you the best resulsts.

Incidentally, I hope you field isn't really called DATE as that's a reserved word...

Upvotes: 1

Related Questions