Oracle uses reverse indexing to promote ORDER by DESC performance __oracle

Using the reverse index (index DESC), you can significantly increase the performance of SQL statements with an ORDER BY DESC clause.

Another way is to create a reverse index, such as:
CREATE INDEX T_IDX on table_name (time DESC);
This creation, the leftmost value of the index is the latest time to insert the data, as if the data is to the left, when the index from left to right scan is the maximum time, but also more practical.

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	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 vs. Execution plan	Description Object owner Object name Cost Cardinality Bytes SELECT STATEMENT GOAL = first_rows p>94103 93792 9097824  table A ccess by INDEX ROWID SYS test_t 94103 93792 9097824  index range scan Descen DING SYS Idx_test_t_id_asc 287 937

Summarize:

Index mode	Cost	Execution plan
(owner,object_id desc)	4	INDEX RANGE SCAN
(owner,object_id)	94103	INDEX RANGE SCAN desending

Third, the actual test process

SQL statements: SELECT * from test_t where owner= ' SYS ' and object_id > 50000 ORDER BY object_id DESC

1, using b* tree reverse indexing (DESC) test process and results

(1) Reverse index (owner+object_id DESC) created on the test_t table

Sql>create index Idx_test_t_id_desc on test_t (owner,object_id DESC)

(2) Re-collect statistical information

Sql> execdbms_stats.gather_table_stats, ownname=> ' SYS ', tabname => ' test_t ',estimate_percent=> 20, Cascade=> TRUE);

(3) SQL execution plan and cost

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, b* tree index (default is ASC ascending) test process and results

(1) The normal default ASC index (OWNER+OBJECT_ID) created on the test_t table

Sql>create index IDX_TEST_T_ID_ASC on test_t (owner,object_id)

(2) Re-collect statistical information

Sql> execdbms_stats.gather_table_stats, ownname=> ' SYS ', tabname => ' test_t ',estimate_percent=> 20, Cascade=> TRUE);

(3) SQL execution plan and cost

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

This article is synchronized on the official forum of the sub-RTHK optimization team, link address: http://dba.oraclefans.cn/discuz/forum.php?mod=viewthread&tid=1208&fromuid=