Databases Reference
In-Depth Information
The following is another example that shows how the database prefers to perform a full table scan
when the query predicate requires searching a large proportion of a table's rows; the condition
a > 8000
obviously requires scanning more data than the condition
a > 20000
.
SQL> select * from test where a > 8000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 220K| 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 2000 | 220K| 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A">8000)
SQL>
An
index range scan descending
operation is very similar to an index range scan; the difference is
that the database engine reads the results in descending order. One reason the optimizer might make
that choice is to avoid a sort down the road. The cost optimizer will use an index range scan descending
operation when you specify the
ORDER BY <column_name> DESC
clause and the index can satisfy the
clause, thereby avoiding a descending sort operation. An
INDEX RANGE SCAN DESCENDING
operation reads
the index backwards in order to avoid having to read it in its normal order (ascending) and then execute
a sort operation.
SQL> select * from test where a between 1000 and 2000 order by a desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 1184130301
----------------------------------------------------------------------------
| Id|Operation |Name |Rows|Bytes|Cost (%CPU)|Time |
----------------------------------------------------------------------------
| 0|SELECT STATEMENT | |1001| 110K| 11 (0)|00:00:01|
| 1| TABLE ACCESS BY INDEX ROWID |TEST |1001| 110K| 11 (0)|00:00:01|
|* 2| INDEX RANGE SCAN DESCENDING|TEST_PK1|1001| | 4 (0)|00:00:01|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A">=1000 AND "A"<=2000)
SQL>
