当前位置:操作系统 > Unix/Linux >>

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上的索引!'
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,