CODEWITHSUNDEEP
sqloracle-database
Evgeni Dimitrov
Evgeni Dimitrov

Reputation: 22506

Oracle Index is not used when compare timestamp with <= or >=

I have indexes on two columns of type Timestamp(2) START_DATE and END_DATE and a query with WHARE clause like:

WHERE C.START_DATE <= sysdate
  AND sysdate <= C.END_DATE

The explain plan makes TABLE ACCESS FULL instead of Index range scan.

-If I change <= to = the index is used.

-If I change sysdate to systimestamp it's worse in the explain plan:

SYS_EXTRACT_UTC(INTERNAL_FUNCTION(C.START_DATE))<=SYS_EXTRACT_UTC(SYSTIMESTAMP(6))

with FULL SCAN again.

-Using TO_TIMESTAMP brings the desired index range scan:

WHERE C.START_DATE <= TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
         AND TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') <= C.END_DATE

but I need to use the current date.

Can someone explain why that happens?

EDIT: These are simplified versions of the query, but the same behavior appears. Explain plans.

EXPLAIN PLAN FOR
         select * 
           from communication c
            WHERE C.CAMPAIGN_START_DATE <= sysdate
         AND sysdate <= C.CAMPAIGN_END_DATE;

Plan hash value: 4120205924

-------------------------------------------
| Id  | Operation         | Name          |
-------------------------------------------
|   0 | SELECT STATEMENT  |               |
|   1 |  TABLE ACCESS FULL| COMMUNICATION |
-------------------------------------------

.

EXPLAIN PLAN FOR
         select * 
           from communication c
            WHERE C.START_DATE = sysdate
         AND sysdate = C.END_DATE;

Plan hash value: 1474125303

-------------------------------------------------------------------
| Id  | Operation                        | Name                   |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                        |
|   1 |  TABLE ACCESS BY INDEX ROWID     | COMMUNICATION          |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                        |
|   3 |    BITMAP AND                    |                        |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                        |
|   5 |      INDEX RANGE SCAN            | COMMUNICATION_START_DT |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                        |
|   7 |      INDEX RANGE SCAN            | COMMUNICATION_END_TD   |
-------------------------------------------------------------------

Upvotes: 1

Views: 6389

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

Did you try with today day in timestamp? TO_TIMESTAMP ('27-oct-15 14:10:10.123000' ? Doesnt make sense you compare the query using two different days.

My guess is using current date and <= will mean check most of the rows anyway. That mean using the index wont give much benefit

Try this query.

SELECT COUNT(*)

and 

SELECT COUNT(*)
FROM ....
WHERE C.START_DATE <= sysdate
  AND sysdate <= C.END_DATE

If both COUNT(*) are very similar mean there isnt any benefit on using the index because will need scan the full table anyway.

Upvotes: 3

Related Questions