CODEWITHSUNDEEP
oracle-databaseplsqldeveloper
ColinMac
ColinMac

Reputation: 640

How to use Oracle indexed field in a query

I have a large Oracle table with an indexed date_time field: "DISCONNECT_DATE"

When I use the following where clause my query runs quickly:

DISCONNECT_DATE > TO_DATE('01-DEC-2016', 'DD-MON-YYYY') AND 
DISCONNECT_DATE < TO_DATE('01-JAN-2017', 'DD-MON-YYYY')

When I use the following where clause my query runs (very) slowly:

extract(month from disconnect_date) = '12' and 
extract(year from disconnect_date) = '2016'

They are both more or less equivalent in their intentions. Why does the former work and the later not? (I don't think I have this problem in SQL SERVER)

(I am using PL SQL Developer to write the query)

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

The issue is the use of indexes. In the first, all the functions are on the "constant" side, not on the "column" side. So, Oracle can readily see that an index can be applied.

The logic that does indexing, though, doesn't understand extract(), so the index doesn't get used. If you want to use that construct, you can create an index on function calls:

create index idx_t_ddyear_ddmonth on t(extract(month from disconnect_date), extract(year from disconnect_date));

Note: extract() returns a number not a string, so you should get rid of the single quotes. Mixing data types can also confuse the optimizer.

Upvotes: 3

Related Questions