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
Query Optimization - Simplified Conditions
Subscribe to:
Post Comments (Atom)
I thought maybe the optimizer would've fixed this but I just checked it and you're right. Nice one.
ReplyDelete