Function based index

I was doing some performace and tuning for one of our databases

when i saw the following query

select * from debug_log WHERE TRUNC(TO_DATE(SUBSTR(LOG_TIJD,1,10),'YYYY-MM-DD'))+3 < TRUNC(SYSDATE) AND ROWNUM <= :B1

execution time was 17 sec.

Explain plan was

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1648799776

--------------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |   238 | 29512 | 17034  (14)| 00:00:17 |

|*  1 |  COUNT STOPKEY     |           |       |       |            |          |

|*  2 |   TABLE ACCESS FULL| DEBUG_LOG |   238 | 29512 | 17034  (14)| 00:00:17 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<=TO_NUMBER(:B1))

   2 - filter(TRUNC(TO_DATE(SUBSTR("LOG_TIJD",1,10),'YYYY-MM-DD'))+3<TRU

              NC(SYSDATE@!))

There was no index on the log_tijd colum
Create the index on that colum

 

create index srs4_own.bug_idx on srs4_own.debug_log

(LOG_TIJD)

NOLOGGING

COMPUTE STATISTICS

ONLINE;

Would that be quicker

--------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1648799776

--------------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |   238 | 29512 | 17034  (14)| 00:00:17 |

|*  1 |  COUNT STOPKEY     |           |       |       |            |          |

|*  2 |   TABLE ACCESS FULL| DEBUG_LOG |   238 | 29512 | 17034  (14)| 00:00:17 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<=TO_NUMBER(:B1))

   2 - filter(TRUNC(TO_DATE(SUBSTR("LOG_TIJD",1,10),'YYYY-MM-DD'))+3<TRU

              NC(SYSDATE@!))

No because on the colum log_tijd a function is used (truc,to_data,substr)
What the do now. Well simply create a function based index.

SQL> create index srs4_own.bug_idx on srs4_own.debug_log

    (TRUNC(TO_DATE(SUBSTR("LOG_TIJD",1,10),'YYYY-MM-DD'))+3)

     NOLOGGING

     COMPUTE STATISTICS

     ONLINE;

Would that be quicker

--------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1095413060

------------------------------------------------------------------------------------------

| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |           |   238 | 29512 |     4   (0)| 00:00:01 |

|*  1 |  COUNT STOPKEY               |           |       |       |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEBUG_LOG |   238 | 29512 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | BUG_IDX   |    85 |       |     2   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<=TO_NUMBER(:B1))

   3 - access(TRUNC(TO_DATE(SUBSTR("LOG_TIJD",1,10),'YYYY-MM-DD'))+3<TRUNC(SYSDATE

              @!))

It now takes 5 milliseconde to finish.