本篇文章主要討論rownum和排序以及索引的關系
問題1、rownum正序是指什么?
問題2、rownum和order by語句在一起時,執(zhí)行的先后順序是什么?order by語句如何影響rownum正序?
問題3、rownnum與索引之間的關系?
-- Create table
create table DAIMIN
( ID NUMBER not null,
PARENTID NUMBER
);
alter tabledaimin add constraint pk_daimin primary key(id);
SQL> select*from DAIMIN;
ID PARENTID
---------- ----------
1 6
2 3
5 1
3 2
問題1、rownum正序是指什么?
rownum正序是指rownum的返回,是按照1,2,3依次遞增,如上面的查詢顯示的效果
問題2、rownum和orderby語句在一起時,執(zhí)行的先后順序是什么?order by語句如何影響rownum正序?
下面執(zhí)行兩個語句進行比較:
SQL> select rownum as r, t.ID from daimin t order by t.ID;
R ID
---------- ----------
1 1
2 2
3 3
4 5
現(xiàn)象:是rownum是按照正序來返回的
該語句的執(zhí)行計劃如下:
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | COUNT | | | | |
| 2 |INDEX FULL SCAN | PK_DAIMIN | | | |
--------------------------------------------------------------------
現(xiàn)象:使用了PK_DAIMIN這個主鍵索引,并且從執(zhí)行計劃中看出COUNT操作之后沒有SORT ORDER BY操作
(Rownum事實上在COUNT (STOPKEY)時產(chǎn)生)
SQL> select rownum as r,t.parentid from daimin t order by t.parentid;
R PARENTID
---------- ----------
3 1
4 2
2 3
1 6
現(xiàn)象:是rownum沒有按照正序來返回
該語句的執(zhí)行計劃如下:
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT ORDER BY | | | | |
| 2 | COUNT | | | | |
| 3 | TABLE ACCESS FULL | DAIMIN | | | |
--------------------------------------------------------------------
現(xiàn)象:沒有使用索引查詢 ,并且在COUNT操作之后有SORT ORDER BY操作
分析:為什么這兩句SQL語句返回的rownum不一樣呢?
主要是由于第一句使用了索引已經(jīng)排好序,然后再產(chǎn)生Rownum,此時已經(jīng)是按照parentid排好序的順序,
再按照parentid排序之后原來的rownum標識不會被打亂,所以返回的rownum是正序;
而第二句則使用全表掃描,在全表掃面是查詢出來的結果集是按照表中原有的記錄的先后順序來返回的,
然后在COUNT操作時給返回的記錄標記1,2,3,在標好標記之后再按照parentid字段排序,這樣就將原來
在COUNT時的順序打亂,重新排序,所以返回的rownum不是正序。
問題3、rownnum與索引之間的關系?是不是建了某個字段的索引都會使用索引呢(在沒有改字段的where條件的情況下)?
下面做測試試驗:
給DAIMIN表中的PARENTID字段建索引
createindex DM_PARENTID on DAIMIN (PARENTID);
select rownum as r,t.parentid from daimin t order by t.parentid;
其執(zhí)行計劃:
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT ORDER BY | | | | |
| 2 | COUNT | | | | |
| 3 | TABLE ACCESS FULL | DAIMIN | | | |
--------------------------------------------------------------------
查詢結果:
SQL> select rownum as r, t.parentid from daimin torder by t.parentid;
R PARENTID
---------- ----------
3 1
4 2
2 3
1 6
分析:給parentid字段建了索引,但是該語句并沒有使用索引,仍然采用的是全表掃描,所以返回的rownum仍然不是正序。
如果修改DAIMIN表中的PARENTID字段為not null
alter table DAIMIN modify PARENTID not null;
再次查詢該語句的其執(zhí)行計劃:
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | COUNT | | | | |
| 2 |INDEX FULL SCAN | DM_PARENTID | | | |
---------------------------------------------------------------------
查詢結果:
SQL> select rownum as r, t.parentid from daimin torder by t.parentid;
R PARENTID
---------- ----------
1 1
2 2
3 3
4 6
分析:將parentid字段設置為not null字段之后,由于在order byparentid時會采用parentid字段的索引DM_PARENTID,
所以改語句查詢出來的rownum的順序是正序。
問題:為什么給parentid字段設置為not null字段之后,此時該查詢語句會使用索引呢(在沒有該字段的where條件時)?
這里主要是牽涉到order by使用索引的條件,什么情況下order by會使用索引?
order by使用索引是有條件的:
1)ORDER BY中所有的列必須包含在相同的索引中并保持在索引中的排列順序.
2)ORDER BY中所有的列必須定義為非空.
3)另外,如果ORDER BY中的列在where 條件中出現(xiàn),也會使用索引
上面的試驗是滿足了ORDER BY中列parentid為非空,所以order by parentid使用parentid字段的索引。
對于復合索引:
create index DM_INDEX on DAIMIN (ID, PARENTID);
以下幾句都使用了索引:
Select rownum, t.* from DAIMIN t order by ID,PARENTID; --使用復合索引DM_INDEX
select rownum, t.* from DAIMIN t orderby t.ID desc, t.PARENTID desc; --使用復合索引DM_INDEX
Select rownum, t.*from DAIMIN t order by ID; --使用主鍵索引
Select rownum, t.* fromDAIMIN t order by ID desc; --使用主鍵索引
以下幾句不使用索引:
selectrownum, t.* from DAIMIN t order by t.ID asc, t.PARENTID desc;--不使用復合索引DM_INDEX,全表掃描
Select rownum, t.* from DAIMIN t order byPARENTID;
(該句parentid字段還沒有設置為notnull時,只有復合索引DM_INDEX,此時不使用復合索引)
總結:
1)默認情況是按順序先取rownum,再order by
2)如果order by 滿足使用索引的情況,則先order by,再取rownum
order by 使用索引是有條件的:
1)ORDER BY中所有的列必須包含在相同的索引中并保持在索引中的排列順序.
2)ORDER BY中所有的列必須定義為非空.
3)另外,如果ORDER BY中的列在where 條件中出現(xiàn),也會使用索引