Using reverse indexing to promote order by DESC performance

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