Query Optimization - Simplified Conditions

Oracle Query Optimization - Simplified Conditions

Lets say we have following query to optimize..

SQL> SELECT * FROM MYEMP WHERE HIREDATE + 60 > SYSDATE;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2418123361

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    87 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MYEMP |     1 |    87 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Even if we create an Index on MYEMP(HIREDATE) index will not be used, 
because the field used in where clause in computed field (HIREDATE + 60).

SQL> CREATE INDEX MYEMP_HIREDATE_INDX ON MYEMP(HIREDATE);

Index created.

SQL> SELECT * FROM MYEMP WHERE HIREDATE + 60 > SYSDATE;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2418123361

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    87 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MYEMP |     1 |    87 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

So here we need to simplify the condition as below, the query will start using index.

SQL> SELECT * FROM MYEMP WHERE HIREDATE > SYSDATE - 60;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 334307922

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |    87 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYEMP               |     1 |    87 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MYEMP_HIREDATE_INDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------



Get More Query Optimization Tips:
http://nimishgarg.blogspot.in/2012/10/query-optimization-tips-for-oracle.html