Query Optimization - Join Query

Oracle Query Optimization of Join Query.

Lets say we have following query to be optimized.
which currently is not using indexes.

SQL> SELECT ENAME, DNAME FROM MYEMP, MYDEPT WHERE MYEMP.DEPTNO=MYDEPT.DEPTNO AND EMPNO=7839;

Execution Plan
----------------------------------------------------------
Plan hash value: 3072374081

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    55 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |        |     1 |    55 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| MYEMP  |     1 |    33 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| MYDEPT |     4 |    88 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Here we can simply create index on MYEMP(EMPNO), 
as it is filtering the data most using where clause.

SQL> CREATE INDEX MYEMP_EMPNO_INDX ON MYEMP(EMPNO);

Index created.

SQL> SELECT ENAME, DNAME FROM MYEMP, MYDEPT WHERE MYEMP.DEPTNO=MYDEPT.DEPTNO AND EMPNO=7839;

Execution Plan
----------------------------------------------------------
Plan hash value: 3503326722

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     1 |    55 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN                   |                  |     1 |    55 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MYEMP            |     1 |    33 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | MYEMP_EMPNO_INDX |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | MYDEPT           |     4 |    88 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

We can notice here that MYEMP table is being used by index,
but MYDEPT is not using indexes.
So we can create an index on MYDEPT(DEPTNO) which is used in JOINING both tables.

SQL> CREATE INDEX MYDEPT_DEPTNO_INDX ON MYDEPT(DEPTNO);

Index created.

SQL> SELECT ENAME, DNAME FROM MYEMP, MYDEPT WHERE MYEMP.DEPTNO=MYDEPT.DEPTNO AND EMPNO=7839;

Execution Plan
----------------------------------------------------------
Plan hash value: 2298462778

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                    |     1 |    55 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                    |       |       |            |          |
|   2 |   NESTED LOOPS                |                    |     1 |    55 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| MYEMP              |     1 |    33 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | MYEMP_EMPNO_INDX   |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | MYDEPT_DEPTNO_INDX |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | MYDEPT             |     1 |    22 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------



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