oracle建倒序索引,使用倒序索引提升ORDER BY DESC性能

使用倒序索引提升ORDER BY DESC性能

类别：Oracle数据库   作者：码皇   来源：踩点 的DBA生涯     点击：

使用倒序索引提升ORDER BY DESC性能使用倒序索引(INDEX DESC)，可以大幅提升带有order by desc子句的SQL语句性能。一、场景1、表名：test_t，有一字段名为object_id2、总数据量：580000行，segment_size：

使用倒序索引提升ORDER BY DESC性能 使用倒序索引(INDEX DESC)，可以大幅提升带有order by desc子句的SQL语句性能。 一、场景1、表名：test_t，有一字段名为object_id2、总数据量：580000行，segment_size：72MB3、Where条件(Owner=’SYS’ and Object_id>50000)的行数：32472行4、SQL语句：select * from test_t where owner='SYS' and object_id > 50000 order by object_id desc5、希望借助倒序索引，提升order by object_id desc的性能 二、B*树倒序索引(DESC)比 B*树索引(默认为ASC升序)带来的性能提升对比1、采用B*树倒序索引(DESC)，COST：4，执行计划走的INDEX RANGE SCANSQL语句：select * from test_t where owner='SYS' and object_id > 50000 order by object_id desc索引方式create index idx_test_t_id_DESC on test_t(owner,object_id desc)COST与执行计划DescriptionObject ownerObject nameCostCardinalityBytesSELECT STATEMENT, GOAL = FIRST_ROWS　　4942989806992 TABLE ACCESS BY INDEX ROWIDSYSTEST_T4942989806992  INDEX RANGE SCANSYSIDX_TEST_T_ID_DESC31　 2、采用B*树索引(默认为ASC)，COST：94103，执行计划走的INDEX RANGE SCAN DESENDING索引方式create index idx_test_t_id_DESC on test_t(owner,object_id)COST与执行计划DescriptionObject ownerObject nameCostCardinalityBytesSELECT STATEMENT, GOAL = FIRST_ROWS　　94103937929097824 TABLE ACCESS BY INDEX ROWIDSYSTEST_T94103937929097824  INDEX RANGE SCAN DESCENDINGSYSIDX_TEST_T_ID_ASC28793792　 总结：索引方式COST执行计划(owner,object_id desc)4INDEX RANGE SCAN(owner,object_id)94103INDEX RANGE SCAN DESENDING 三、实际测试过程SQL语句：select * from test_t where owner='SYS' and object_id > 50000 order by object_id desc1、采用B*树倒序索引(DESC)测试过程与结果(1)test_t表上创建(OWNER+OBJECT_ID DESC)的倒序索引   SQL>create index idx_test_t_id_DESC on test_t(owner,object_id desc)(2)重新收集统计信息  SQL> execdbms_stats.gather_table_stats(ownname=>'SYS',tabname => 'TEST_T',estimate_percent=> 20 , cascade=> TRUE );(3)SQL执行计划与COST  DescriptionObject ownerObject nameCostCardinalityBytesSELECT STATEMENT, GOAL = FIRST_ROWS　　4942989806992 TABLE ACCESS BY INDEX ROWIDSYSTEST_T4942989806992  INDEX RANGE SCANSYSIDX_TEST_T_ID_DESC31　2、B*树索引(默认为ASC升序)测试过程与结果(1)test_t表上创建(OWNER+OBJECT_ID)的普通默认ASC索引  SQL>create index idx_test_t_id_ASC on test_t(owner,object_id)(2)重新收集统计信息  SQL> execdbms_stats.gather_table_stats(ownname=>'SYS',tabname => 'TEST_T',estimate_percent=> 20 , cascade=> TRUE );(3)SQL执行计划与COST DescriptionObject ownerObject nameCostCardinalityBytesSELECT STATEMENT, GOAL = FIRST_ROWS　　94103937929097824 TABLE ACCESS BY INDEX ROWIDSYSTEST_T94103937929097824  INDEX RANGE SCAN DESCENDINGSYSIDX_TEST_T_ID_ASC28793792

相关热词搜索：

使用 索引 提升