Indexes in Oracle

The below post shows the benefits of creating indexes on a table and the usage of a fast full scan feature for optimizing query search results using indexes.

For this example, the employees table and data for employees table can be created from links below

Archiving: A generic solution - Part 1 

Alternatively from github

DB Sample Schema : Human Resources

The below query is to select employee name from the department table for department id 10. We take the explain plan for it as below.

EXPLAIN PLAN FOR
SELECT FIRST_NAME || ' ' || LAST_NAME emp_name
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

The plan for this is below. The cost of the query execution shows 3.

Plan hash value: 1445457117
 
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    18 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    18 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DEPARTMENT_ID"=10)

We now create an index on EMPLOYEES table DEPARTMENT_ID column and check the plan.

CREATE INDEX IDX_DEPT_ID ON EMPLOYEES(DEPARTMENT_ID);

The plan shows the cost has reduced to 2 due to the index search instead of the FULL TABLE SCAN.

Plan hash value: 1576505688
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |     1 |    18 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES   |     1 |    18 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_DEPT_ID |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEPARTMENT_ID"=10) 

Since we are selecting only the employee name, we can consider adding the employee name as part of the index. In that case the entire fetch will be from the employee index tree and it does not have to search the employee table on index rowid. 

This data fetch directly from the index table is called a fast full scan.

DROP INDEX IDX_DEPT_ID;
CREATE INDEX IDX_DEPT_ID ON EMPLOYEES(DEPARTMENT_ID, FIRST_NAME || ' ' || LAST_NAME);

We now check the plan of the query

Plan hash value: 3831909367
 
--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     1 |    28 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_DEPT_ID |     1 |    28 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("DEPARTMENT_ID"=10)

The plan shows the cost has further reduced to 1 due to an oracle index FAST FULL SCAN.

References

Books : Oracle9i Performance Tuning Tips & Techniques 

              by Richard J. Niemiec

God's Word for the day

The prince and the judge and the ruler are honored, 

  but none of them is greater than the one who fears the Lord. 

Free citizens will serve a wise servant, 

  and an intelligent person will not complain.

Sirach 10:24-25