order by使用索引的情况
order by使用索引的情况
问题:
id为PK
select a,b,id from adf where rownum<7 A B ID ---------------------- ---------------------- ------ 0 a 1 0 d 2 0 c 333 0 b 4 0 a 5 1 a 66 6 rows selected select a,b,id from adf where rownum<7 order by id A B ID ---------------------- ---------------------- ------ 0 a 1 0 d 2 0 b 4 0 a 5 1 b 7 1 c 8 6 rows selected
结果是先order by ,再执行rownum<7
但如果是这样:
select a,b,id from adf where rownum<7 order by b A B ID ---------------------- ---------------------- ------ 0 a 1 0 a 5 1 a 66 0 b 4 0 c 333 0 d 2 6 rows selected
结果是先执行rownum<7,再order by
这是为什么???
难道是因为id是PK,所以就不同。
~~~~~~~~~~~~~~~~~~~~~~
解答:
跟主健及其上面的索引有关系:
ID是主健.
SQL> select * from t; ID ID1 ---------- ---------- 1 2 2 4 4 3 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'T' SQL> select * from t where rownum<3 order by id desc; ID ID1 ---------- ---------- 4 3 2 4 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 COUNT (STOPKEY) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' 3 2 INDEX (FULL SCAN DESCENDING) OF 'PK' (UNIQUE) '因为是主健,先进行索引的排序全表扫描,然后在count 2' SQL> select * from t where rownum<3 order by id1; ID ID1 ---------- ---------- 1 2 2 4 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY) 2 1 COUNT (STOPKEY) 3 2 TABLE ACCESS (FULL) OF 'T'
'没有使用索引,先进行全表扫描,然后再count 2,再排序'
为什么会使用pk的索引呢?一般的索引行吗?
我们看看:
1)在id1上创建索引
SQL> create index id1idx on t(id1); 索引已创建。 SQL> select * from t where rownum<3 order by id1; ID ID1 ---------- ---------- 1 2 2 4 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY) 2 1 COUNT (STOPKEY) 3 2 TABLE ACCESS (FULL) OF 'T' '还是全表扫描' SQL> select * from t where id1>0 and rownum<3 order by id1; '加了id1>0 ,这样会使用到id1的索引' ID ID1 ---------- ---------- 1 2 4 3 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 COUNT (STOPKEY) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' 3 2 INDEX (RANGE SCAN) OF 'ID1IDX' (NON-UNIQUE)
2)pk的索引是因为有order by 而使用的。order by 使用索引是有条件的:
ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
ORDER BY中所有的列必须定义为非空.
因为id1是可以为空的,所以order by 不会使用它。而pk的索引是非空的,所以order by 会使用。
我们将id1设为非空看看。
SQL> alter table t 2 modify (id1 int not null);
表已更改。
SQL> select * from t where rownum<3 order by id1; ID ID1 ---------- ---------- 1 2 4 3 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 COUNT (STOPKEY) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' 3 2 INDEX (FULL SCAN) OF 'ID1IDX' (NON-UNIQUE)
'这次,order by id1 使用了id1上的索引!'