Query Optimization - Single table

Oracle Query Optimization - Single table

If we are using a query on single table with where clause on a column,
it can be simply optimized by creating an Index. 
If the query is fetching near 10% data, index would be used by query.

Example:

SQL> set autot trace
SQL> SELECT * FROM MYEMP WHERE EMPNO=7839;

Elapsed: 00:00:00.02

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 |
---------------------------------------------------------------------------

SQL> CREATE INDEX MYEMP_EMPNO_INDX ON MYEMP(EMPNO);

Index created.

Elapsed: 00:00:00.07
SQL> SELECT * FROM MYEMP WHERE EMPNO=7839;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 254813117

------------------------------------------------------------------------------------------------
| 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_EMPNO_INDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------



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