Sorts can be avoided by creating index with asc or desc attribute?

[signalsys]

I have the following tests with tables in scott's schemas:
SQL> set autotrace trace
SQL> select * from emp order by ename;

14 rows selected.

Elapsed: 00:00:01.26

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=448)
1 0 SORT (ORDER BY) (Cost=5 Card=14 Bytes=448)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=448)




Statistics
----------------------------------------------------------
188 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
1506 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
14 rows processed

Then i create an ascending index on ename:
SQL> create index ind_emp_ename on emp(ename asc);
SQL> select * from emp order by ename;

14 rows selected.

Elapsed: 00:00:01.26

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=448)
1 0 SORT (ORDER BY) (Cost=5 Card=14 Bytes=448)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=448)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1506 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed

As you see there isn't any improvement in the execute plan. It seems that the ascending index on ename does not work.

 

[stefanhei]

Hi,

As there is no where-clause the whole table has to be read anyway, and the table is small enough to sort in memory.
So why should the index (which the optimizer has no knowledge of, as there was no analyze) be read, which would mean additional disk operation.

Stefan

 

[ChrisHunt]

If you explain the plans for [tt]SELECT ename FROM emp ORDER BY ename[/tt] you may see some difference in the plan. Also notice that the numbers in the stats are lower for the indexed example, though that may just be coincidence.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[LKBrwnDBA]

Maybe it is something else, because I get different results:

SQL>select * from emp order by ename;

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     TABLE ACCESS (FULL) OF 'EMP'




Statistics
----------------------------------------------------------
         63  recursive calls
         12  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       1976  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>create index ename_ix0 on emp(ename);

Index created.

SQL>select * from emp order by ename;

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     INDEX (FULL SCAN) OF 'ENAME_IX0' (NON-UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1976  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb