Query Optimization - OR and Union

When we execute query with OR clause where OR is filtering the records from 2 
different field. Oracle Database can not use index to filter the data.

For example:

SQL> CREATE INDEX MYEMP_EMPNO_INDX ON MYEMP(EMPNO);

Index created.

SQL> CREATE INDEX MYEMP_HIREDATE_INDX ON MYEMP(HIREDATE);

Index created.

SQL> SET AUTOT TRACE
SQL> SELECT * FROM MYEMP WHERE EMPNO=7839 OR HIREDATE > SYSDATE - 60;

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

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

As here we can see that we have indexes on EMPNO and HIREDATE of EMP table,
But the query with EMPNO=7839 OR HIREDATE > SYSDATE - 60 condition was not able 
to use any index.

Workaround of this problem is to break this OR clause into UNION, 
so that index can be used by the Oracle Database

Solution:

SQL> SELECT * FROM MYEMP WHERE EMPNO=7839
  UNION
  SELECT * FROM MYEMP WHERE HIREDATE > SYSDATE - 60;

Execution Plan
----------------------------------------------------------
Plan hash value: 1491074982

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     2 |    76 |     6  (67)| 00:00:01 |
|   1 |  SORT UNIQUE                  |                     |     2 |    76 |     6  (67)| 00:00:01 |
|   2 |   UNION-ALL                   |                     |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| MYEMP               |     1 |    38 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | MYEMP_EMPNO_INDX    |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| MYEMP               |     1 |    38 |     2   (0)| 00:00:01 |
|*  6 |     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

4 comments:

AnonymousMarch 2, 2013 at 6:00 AM
I don't think this applies to 11.2. I just tested it on 11.2 and it uses the indexes with the "OR" query:

1* SELECT * FROM EMP WHERE EMPNO=7839 OR HIREDATE > SYSDATE - 60
SCOTT@ORCL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7839 KING PRESIDENT 1981-11-17 5000 10

Execution Plan
----------------------------------------------------------
Plan hash value: 1944999720

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 76 | 3 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | HIREDATE_IX | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("HIREDATE">SYSDATE@!-60)
4 - filter(LNNVL("HIREDATE">SYSDATE@!-60))
5 - access("EMPNO"=7839)
UnknownJanuary 8, 2016 at 7:17 PM
I have one question here... The cost and time is so low without index and after index is used the cost is raise and even execution time is raise so why should I use index here?