order by使用索引 要保證那個(gè)列設(shè)置為非空

以下只在oracle10g中測試：

order by 使用索引是有條件的(只包括在有選擇的此列上，不一定適應(yīng)*的情況)：

1)ORDER BY中所有的列必須包含在相同的索引中并保持在索引中的排列順序.
2)ORDER BY中所有的列必須定義為非空.

請(qǐng)看下面的測試：

id為PK，a上有一般索引,b列沒有索引

執(zhí)行下面兩條語句，結(jié)果是先order by ,再執(zhí)行rownum<20

（1）select * from tb where rownum<20 order by id;

但如果是這樣：

（2）select * from tb where rownum<20 order by b;

(3) select * from tb where rownum<20 order by a;

結(jié)果是先執(zhí)行rownum<7,再order by

這是為什么呢，跟主健及其上面的索引有關(guān)系：

再看看下面的測試：----------------------------------------------------------------------------------------------------------

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)
'因?yàn)槭侵鹘?，先進(jìn)行索引的排序全表掃描，然后在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'
'沒有使用索引，先進(jìn)行全表掃描，然后再count 2，再排序'

為什么會(huì)使用pk的索引呢？一般的索引行嗎？
我們看看：

1）在id1上創(chuàng)建索引

SQL> create index id1idx on t(id1);

索引已創(chuàng)建。
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 ，這樣會(huì)使用到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的索引是因?yàn)橛衞rder by 而使用的。order by 使用索引是有條件的：
ORDER BY中所有的列必須包含在相同的索引中并保持在索引中的排列順序.
ORDER BY中所有的列必須定義為非空.

因?yàn)閕d1是可以為空的，所以order by 不會(huì)使用它。而pk的索引是非空的，所以order by 會(huì)使用。

我們將id1設(shè)為非空看看。

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上的索引！'

說明 如果該表上尚未創(chuàng)建聚集索引，且在創(chuàng)建 PRIMARY KEY 約束時(shí)未指定非聚集索引，PRIMARY KEY 約束會(huì)自動(dòng)創(chuàng)建聚集索引。

order by 使用索引的情況