Dear Oracle Experts,
I have been reading Oracle documents about index access paths and their usage. It is written that Index full scan can be used by the optimizer when the query contains ORDER BY clause. After reading this document, a simple question comes to my mind. Does it worth to use index full scan for sorting? Can't the sorting operation enough? What do you think?
Consider the following example, First optimizer retrieves all data from index, then access to the table by each rowid. Ok, I understand that the data is sorted in the index but my question is why optimizer do full table scan then sort the data? Does the sorting operation cost more than the index operation? I think index full scan is unnecessary. What do you think?
SQL> select * from test order by a;
Execution Plan
----------------------------------------------------------
Plan hash value: 3311708430
---------------------------------------------------------------------------
| Id|Operation |Name |Rows|Bytes|Cost (%CPU)|Time |
---------------------------------------------------------------------------
| 0|SELECT STATEMENT | |9433|1040K| 79 (0)|00:00:03|
| 1| TABLE ACCESS BY INDEX ROWID|TEST |9433|1040K| 79 (0)|00:00:03|
| 2| INDEX FULL SCAN |TEST_PK1|9433| | 21 (0)|00:00:01|
---------------------------------------------------------------------------
SQL>
Thanks in advance.