疑问? Oracle普通的B树索引是升序还是降序?
疑问? Oracle普通的B树索引是升序还是降序?
一、测试创建对象
SQL> create table a as select * from dba_objects; Table created. SQL> create index a_id on a(object_id); Index created. SQL> set autotrace on
二、测试索引,观察执行计划
SQL> select object_name from a where object_id in(7788,7799) order by object_id desc; OBJECT_NAME -------------------------------------------------------------------------------------------------------------------------------- V$DIAG_DDE_USR_INC_ACT_MAP V_$DIAG_DDE_USER_ACTION_DEF Execution Plan ---------------------------------------------------------- Plan hash value: 2600461980 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 1106 | 9 (0)| 00:00:01 | | 1 | INLIST ITERATOR | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID | A | 14 | 1106 | 9 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN DESCENDING| A_ID | 299 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OBJECT_ID"=7788 OR "OBJECT_ID"=7799) Note ----- - dynamic sampling used for this statement (level=2) SQL> select object_name from a where object_id in(7788,7799) order by object_id asc; OBJECT_NAME -------------------------------------------------------------------------------------------------------------------------------- V_$DIAG_DDE_USER_ACTION_DEF V$DIAG_DDE_USR_INC_ACT_MAP Execution Plan ---------------------------------------------------------- Plan hash value: 2908601139 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 1106 | 9 (0)| 00:00:01 | | 1 | INLIST ITERATOR | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| A | 14 | 1106 | 9 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | A_ID | 299 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OBJECT_ID"=7788 OR "OBJECT_ID"=7799) Note ----- - dynamic sampling used for this statement (level=2)
可以发现,使用ASC时,执行计划无改变
使用DESC 时,执行计划存在
INDEX RANGE SCAN DESCENDING 索引范围降序扫描,因此本次实验证明,当默认排序方式创建索引时,索引数据使用升序排列。那么问题来了,知道这个东西有啥用呢?创建复合索引,并且业务SQL存在order by 的方式时!!!注意理解,或者SQL order by desc/asc 则创建复合索引列跟着 desc/asc也没毛病。
相关推荐
娜娜 2020-07-18
zhangchaoming 2020-06-21
康慧欣 2020-05-02
Streamoutput 2019-12-02
der 2019-11-27
joyleeLyhua 2015-03-25
zhangchaoming 2020-05-27
LuoXinLoves 2020-05-04
Justdoit00 2020-04-26
oraclemch 2019-12-29
talkingDB 2020-01-11
Streamoutput 2020-01-10
oraclemch 2019-12-01
jbossllx 2019-11-05
静思苑 2019-01-15
人勤阡陌绿 2019-03-27
Cavlier 2019-03-19