using the reverse index (index DESC), you can significantly increase the performance of SQL statements with an ORDER BY DESC clause.
first, the scene
1, table name: test_t, there is a field named object_id
2, total data quantity: 580000 line, SEGMENT_SIZE:72MB
3 Number of rows in the Where condition (owner= ' SYS ' and object_id>50000): 32472 rows
4. SQL statement: SELECT * from test_t where owner= ' SYS ' and object_id > 50000 ORDER BY object_id DESC
5, hope to use the reverse index, enhance the order by object_id DESC performance
second, the b* Tree Reverse index (DESC) than the b* tree index (the default is ASC Ascending) performance improvement comparison
1, the use of b* Tree Reverse index (DESC), cost:4, the implementation of the plan to walk the index RANGE SCAN
SQL statements: SELECT * from test_t where owner= ' SYS ' and object_id > 50000 ORDER BY object_id DESC
Index mode |
CreateIndex Idx_test_t_id_descon test_t (OWNER,OBJECT_IDDESC) |
Cost and implementation Plan |
Description |
Object owner |
Object Name |
Cost |
Cardinality |
Bytes |
SELECT STATEMENT, GOAL = first_rows |
|
|
4 |
94298 |
9806992 |
TABLE ACCESS by INDEX ROWID |
SYS |
test_t |
4 |
94298 |
9806992 |
INDEX RANGE SCAN |
SYS |
Idx_test_t_id_desc |
3 |
1 |
|
|
2, using the b* tree index (the default is ASC), cost:94103, the implementation of the plan to walk the index RANGE SCAN desending
Index mode |
CreateIndex Idx_test_t_id_descon test_t (owner,object_id) |
Cost and implementation Plan |
Description |
Object owner |
Object Name |
Cost |
Cardinality |
Bytes |
SELECT STATEMENT, GOAL = first_rows |
|
|
94103 |
93792 |
9097824 |
TABLE ACCESS by INDEX ROWID |
SYS |
test_t |
94103 |
93792 |
9097824 |
INDEX RANGE SCAN Descending |
SYS |
Idx_test_t_id_asc |
287 |
93792 |
|
|
Summary:
Index mode |
Cost |
Execution plan |
(owner,object_id desc) |
4 |
INDEX RANGE SCAN |