Index Series 10-- ordered Optimization of Index characteristics order by
-indexing and sorting
Drop table t purge
Create table t as select * from dba_objects
Set autotrace traceonly
-- oracle is smart enough not to be foolish enough to sort here, do query conversion, and ignore this sort.
Select count (*) from t order by object_id
-the following statement describes the sort
Set autotrace traceonly
Set linesize 1000
Drop table t purge
Create table t as select * from dba_objects
-- the following statement has no index and order by, so sorting is bound to occur.
Select * from t where object_id > 2 order by object_id
Carry out the plan
| | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | | 92407 | 18m | | 4454 (1) | 00:00:54 |
| | 1 | SORT ORDER BY | | 92407 | 18m | 21m | 4454 (1) | 00:00:54 |
| | * 2 | TABLE ACCESS FULL | T | 92407 | 18m | | 294 (2) | 00:00:04 |
Statistical information
0 recursive calls
0 db block gets
1047 consistent gets
0 physical reads
0 redo size
3513923 bytes sent via SQL*Net to client
54029 bytes received via SQL*Net from client
4876 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
73117 rows processed
-after adding an index, it is possible for Oracle to take advantage of the orderly nature of the index to avoid sorting, as shown below:
Create index idx_t_object_id on t (object_id)
Set autotrace traceonly
Select * from t where object_id > 2 order by object_id
Carry out the plan
-
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-
| | 0 | SELECT STATEMENT | | 92407 | 18m | 1302 (1) | 00:00:16 |
| | 1 | TABLE ACCESS BY INDEX ROWID | T | 92407 | 18m | 1302 (1) | 00:00:16 |
| | * 2 | INDEX RANGE SCAN | IDX_T_OBJECT_ID | 92407 | | 177 (1) | 00:00:03 |
-
Statistical information
0 recursive calls
0 db block gets
10952 consistent gets
0 physical reads
0 redo size
8115221 bytes sent via SQL*Net to client
54029 bytes received via SQL*Net from client
4876 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
73117 rows processed
In the following case, Oracle must not hesitate to choose to use the index, because the return table has been cancelled!
Select object_id from t where object_id > 2 order by object_id
Carry out the plan
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | | 92407 | 1173K | 177K | 00:00:03 |
| | * 1 | INDEX RANGE SCAN | IDX_T_OBJECT_ID | 92407 | 1173K | 177K (1) | 00:00:03 |
Statistical information
0 recursive calls
0 db block gets
5027 consistent gets
0 physical reads
0 redo size
1062289 bytes sent via SQL*Net to client
54029 bytes received via SQL*Net from client
4876 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
73117 rows processed
In addition, Oracle will not need an index if it is the following statement.
Select object_id from t where object_id > 2
