CODEWITHSUNDEEP
oracle-databasesql-execution-plan
aymanzone
aymanzone

Reputation: 135

Why does oracle chose INDEX FULL SCAN followed by ACCESS BY INDEX ROWID vs FULL TABLE SCAN?

Why does oracle chose INDEX FULL SCAN followed by ACCESS BY INDEX ROWID instead of just a full table scan which is one step and does the same thing and probably faster?

why oracle choose

|   2 |   TABLE ACCESS BY INDEX ROWID
|   3 |    INDEX FULL SCAN

over 

|  2 |  TABLE ACCESS FULL|

For further clarification this is the query and full execution plan

SELECT EMP_NO, ENAME, SALARY, dname 
FROM EMP E, DEPT D
WHERE E.DEPT_NO=D.DEPT_NO;



Execution Plan
----------------------------------------------------------
Plan hash value: 2125045483

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |   879 | 35160 |     8  (13)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |   879 | 35160 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     7 |    91 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_PK |     7 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |   879 | 23733 |     6  (17)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |   879 | 23733 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Upvotes: 1

Views: 1726

Answers (2)

Gary Myers
Gary Myers

Reputation: 35401

Typically a table full scan has to read up to the table high water mark (ie pretty much every block that has ever been allocated as used by the table). If there's been a big delete from the table, there might be 10,000 blocks in the table, mostly empty, which it has to read through.

An index is a more complicated structure but an index full scan doesn't have to deal with empty blocks. Also indexes (as they only have a subset of columns) tend to be smaller and tend to sit in the cache longer.

In your example, you've got a cost of 3 as the total for accessing the index and the table from the index. That's pretty low and maybe the table scan came out at 4 or 5 (also low, but still second).

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You'll notice that the optimizer does choose a FULL TABLE SCAN on the EMP table. It then uses the key EMP.DEPT_NO to look up the appropriate value on DEPT because in this case doing the lookup 879 times is apparently faster (at least in the opinion of the optimizer) than doing a FULL TABLE SCAN on DEPT and making 879*7 comparisons.

For fun you can check to see how recently statistics were collected on these tables by looking at the USER_TABLES or DBA_TABLES views:

SELECT TABLE_NAME, LAST_ANALYZED
  FROM USER_TABLES
  WHERE TABLE_NAME IN ('EMP', 'DEPT')

And if you want you can gather fresh stats on these tables by using

BEGIN
  DBMS_STATS.GATHER_TABLE_STATISTICS(OWNNAME => 'your_schema_name',
                                     TABNAME => 'EMP',
                                     CASCADE => TRUE);

  DBMS_STATS.GATHER_TABLE_STATISTICS(OWNNAME => 'your_schema_name',
                                     TABNAME => 'DEPT',
                                     CASCADE => TRUE);
END;

Upvotes: 0

Related Questions