Table access by index rowid is used to retain or access those columns of the WHERE part of the clause which is not accessed by index. For example in below the index EMP_EMP_ID_PK was used to access the column EMPLOYEE_ID. However, the WHERE clause has three more columns PHONE_NUMBER, HIRE_DATE, AND COMMISSION_PCT which are not accessed by index. Hence, from the accessed data of index i.e. EMPLOYEE_ID BETWEEN 100 AND 175, oracle has to manually go to each row and check the hire_date, phone_number and commission_pct matches with the values of where clause. In order to access these three columns, we need table access by index row id. SELECT /*+ Case_Study_1 */ E.EMPLOYEE_ID, E.FIRST_NAME FROM EMPLOYEES E WHERE E.PHONE_NUMBER = '011.44.1344.345268' AND HIRE_DATE > to_date('30-JAN-2004 00:00:00','DD-MON-YYYY HH24:MI:SS') AND E.COMMISSION_PCT IN ( .25 , .15 ) AND E.EMPLOYEE_ID BETWEEN 100 AND 175; EMPLOYEE_ID FIRST_NAME ----------- -------------------- 151 David Plan hash value: 603312277 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 36 | 1 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 36 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 77 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("E"."PHONE_NUMBER"='011.44.1344.429268' AND ("E"."COMMISSION_PCT"=.15 OR "E"."COMMISSION_PCT"=.25) AND "HIRE_DATE">TO_DATE(' 2004-01-30 00:00:00','syyyy-mm-dd hh24:mi:ss')) 2 - access("E"."EMPLOYEE_ID">=100 AND "E"."EMPLOYEE_ID"
Nice one
Thank You :)
Sir, Can you please tell The purpose of Table Access by Index RowId here ?
Table access by index rowid is used to retain or access those columns of the WHERE part of the clause which is not accessed by index.
For example in below the index EMP_EMP_ID_PK was used to access the column EMPLOYEE_ID. However, the WHERE clause has three more columns PHONE_NUMBER, HIRE_DATE, AND COMMISSION_PCT which are not accessed by index. Hence, from the accessed data of index i.e. EMPLOYEE_ID BETWEEN 100 AND 175, oracle has to manually go to each row and check the hire_date, phone_number and commission_pct matches with the values of where clause. In order to access these three columns, we need table access by index row id.
SELECT /*+ Case_Study_1 */ E.EMPLOYEE_ID, E.FIRST_NAME
FROM EMPLOYEES E
WHERE E.PHONE_NUMBER = '011.44.1344.345268'
AND HIRE_DATE > to_date('30-JAN-2004 00:00:00','DD-MON-YYYY HH24:MI:SS')
AND E.COMMISSION_PCT IN ( .25 , .15 )
AND E.EMPLOYEE_ID BETWEEN 100 AND 175;
EMPLOYEE_ID FIRST_NAME
----------- --------------------
151 David
Plan hash value: 603312277
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 36 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 77 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."PHONE_NUMBER"='011.44.1344.429268' AND ("E"."COMMISSION_PCT"=.15 OR "E"."COMMISSION_PCT"=.25) AND "HIRE_DATE">TO_DATE(' 2004-01-30 00:00:00','syyyy-mm-dd hh24:mi:ss'))
2 - access("E"."EMPLOYEE_ID">=100 AND "E"."EMPLOYEE_ID"