溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
單表分頁優(yōu)化思路：
--創(chuàng)建測試表：
SQL> create table t_test as select * from dba_objects;
Table created.
如，下面的sql （沒有過濾條件，只有排序），要將查詢結(jié)果分頁顯示，每頁顯示10條，如：
select * from t_test order by object_id;
例子：
1、分頁查詢sql語句，如下（通常會(huì)采用下面的方法，但是這是錯(cuò)誤的分頁框架）
語法：select * from (select t.*,rownum rn from (需要分頁的sql)t ) where rn >=1 and rn <=10;
SQL> select * from (select t.*,rownum rn from (select * from t_test order by object_id) t) where rn >=1 and rn <=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3603170480
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80700 | 16M| | 3918 (1)| 00:00:48 |
|* 1 | VIEW | | 80700 | 16M| | 3918 (1)| 00:00:48 |
| 2 | COUNT | | | | | | |
| 3 | VIEW | | 80700 | 15M| | 3918 (1)| 00:00:48 |
| 4 | SORT ORDER BY | | 80700 | 15M| 19M| 3918 (1)| 00:00:48 |
| 5 | TABLE ACCESS FULL| t_test | 80700 | 15M| | 287 (1)| 00:00:04 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=10 AND "RN">=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
331 recursive calls
0 db block gets
1211 consistent gets
1024 physical reads
0 redo size
2075 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed從上面的執(zhí)行計(jì)劃可以看出，該sql走了全表掃描，如果該表很大，則先要將該表排序，再取出其中10行數(shù)據(jù)，這樣會(huì)出現(xiàn)嚴(yán)重的性能問題，所以該sql不能走全表掃描，必須走索引掃描。（因?yàn)樗饕呀?jīng)排序了，可使用索引來消除排序）一般分頁語句中都有排序。
如下，在object_id列建立索引，并在索引中添加一個(gè)常量0，如下：（在索引中添加一個(gè)常量0，是因?yàn)閛bject_id列允許位null，如果不添加常量（不一定是0，可以是1,2,3，也可以是英文字母），索引中就不能存儲(chǔ)null值，；因?yàn)閟ql中并沒有剔除null值，所以我們必須要添加一個(gè)常量，讓索引存儲(chǔ)null，這樣才能使sql走索引。）
SQL> create index idx_test on t_test(object_id,0);
SQL> select * from (select t.*,rownum rn from (select /*+ index)t_test idx_test) */ * from t_test order by object_id) t) where rn >=1 and rn <=10;
SQL> alter session set statistics_level=all;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------
SQL_ID gvq2rvmutr85w, child number 0
-------------------------------------
select * from (select t.*,rownum rn from (select /*+ index)t_test
idx_test) */ * from t_test order by object_id) t) where rn >=1 and rn
<=10
Plan hash value: 3119682446
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.08 | 1245 |
|* 1 | VIEW | | 1 | 80700 | 10 |00:00:00.08 | 1245 |
| 2 | COUNT | | 1 | | 71903 |00:00:00.07 | 1245 |
| 3 | VIEW | | 1 | 80700 | 71903 |00:00:00.06 | 1245 |
| 4 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 80700 | 71903 |00:00:00.04 | 1245 |
| 5 | INDEX FULL SCAN | IDX_test | 1 | 80700 | 71903 |00:00:00.01 | 181 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("RN"<=10 AND "RN">=1))
Note
-----
- dynamic sampling used for this statement (level=2)
28 rows selected.(因?yàn)閟ql語句沒有where過濾條件，強(qiáng)制走索引只能走index full scan，無法走索引范圍掃描（index range scan）。E-Rows的顯示 說明了掃描了索引中所有葉子塊，一共消耗了1245個(gè)邏輯讀；理想的執(zhí)行計(jì)劃是：index full scan只掃描一個(gè)最多幾個(gè)索引葉子塊，最根本的原因還在于這個(gè)分頁框架錯(cuò)了！)
2、正確的分頁框架：
語法：select * from (select * from (select a.*,rownum rn from (需要分頁的sql) a ) where rownum <=10) where rn >=1;
SQL> select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test) */ * from t_test order by object_id) a ) where rownum <=10) where rn >=1;
SQL> alter session set statistics_level=all;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------
SQL_ID cgjp65zfj1yqa, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test) */ * from t_test order by object_id) a ) where
rownum <=10) where rn >=1
Plan hash value: 1201925926
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 5 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 5 |
| 3 | VIEW | | 1 | 80700 | 10 |00:00:00.01 | 5 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 5 |
| 5 | VIEW | | 1 | 80700 | 10 |00:00:00.01 | 5 |
| 6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 80700 | 10 |00:00:00.01 | 5 |
| 7 | INDEX FULL SCAN | IDX_test | 1 | 80700 | 10 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
Note
-----
- dynamic sampling used for this statement (level=2)
31 rows selected.(從上面的執(zhí)行計(jì)劃可以看到，sql走 index full scan，只掃描了10條數(shù)據(jù)（id=7 A-Rows=10）就停止了（id=2 COUNT STOPKEY）一共消耗了5個(gè)邏輯讀；該執(zhí)行計(jì)劃利用索引已經(jīng)排序的特性只掃描索引獲取了10條數(shù)據(jù)，然后再利用count stopkey特性，獲取到分頁需要的數(shù)據(jù)，sql立即停止運(yùn)行，這才是最佳的執(zhí)行計(jì)劃。)
注意：
如果分頁語句中有排序（order by）,要利用索引已經(jīng)排序特性，將order by的列包含在索引中，同時(shí)也要利用rownum的count stopkey特性來優(yōu)化分頁sql。如果分頁中沒有排序，可以直接利用rownum的count stopkey特性來優(yōu)化分頁sql。
例子：
如下兩條sq（注意，過濾條件是等值過濾，當(dāng)然也有order by）l，改成分頁語句，并查看執(zhí)行計(jì)劃，如下：
select * from t_test where owner='SCOTT' order by object_id;
select * from t_test where owner='SYS' order by object_id;
(其中第一條sql語句的過濾條件是where owner='SCOTT'；該過濾條件能過濾掉表中大部分?jǐn)?shù)據(jù)。第二條sql語句的過濾條件where owner='SYS'，能過濾表中一半數(shù)據(jù))
---執(zhí)行第一條sql語句：
SQL> select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test) */ * from t_test where owner='SCOTT' order by object_id) a ) where rownum <=10) where rn >=1;
SQL> alter session set statistics_level=all;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------
SQL_ID 0w9tbrwkn9tn6, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test) */ * from t_test where owner='SCOTT' order by
object_id) a ) where rownum <=10) where rn >=1
Plan hash value: 1201925926
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.04 | 1245 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.04 | 1245 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.04 | 1245 |
| 3 | VIEW | | 1 | 12 | 10 |00:00:00.04 | 1245 |
| 4 | COUNT | | 1 | | 10 |00:00:00.04 | 1245 |
| 5 | VIEW | | 1 | 12 | 10 |00:00:00.04 | 1245 |
|* 6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 12 | 10 |00:00:00.04 | 1245 |
| 7 | INDEX FULL SCAN | IDX_test | 1 | 80700 | 71901 |00:00:00.01 | 181 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
6 - filter("OWNER"='SCOTT')
Note
-----
- dynamic sampling used for this statement (level=2)
32 rows selected.---執(zhí)行第2條語句，如下：
SQL> select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test) */ * from t_test where owner='SYS' order by object_id) a ) where rownum <=10) where rn >=1;
SQL> alter session set statistics_level=all;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------
SQL_ID dfwkpppbtc8h7, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test) */ * from t_test where owner='SYS' order by
object_id) a ) where rownum <=10) where rn >=1
Plan hash value: 1201925926
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 5 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 5 |
| 3 | VIEW | | 1 | 28483 | 10 |00:00:00.01 | 5 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 5 |
| 5 | VIEW | | 1 | 28483 | 10 |00:00:00.01 | 5 |
|* 6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 28483 | 10 |00:00:00.01 | 5 |
| 7 | INDEX FULL SCAN | IDX_test | 1 | 80700 | 10 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
6 - filter("OWNER"='SYS')
Note
-----
- dynamic sampling used for this statement (level=2)
32 rows selected.從上面的執(zhí)行計(jì)劃可以看出，兩條sql都走了 index full scan，第1條sql從索引中掃描了71901條數(shù)據(jù)（id=7 A-Rows=71901），在回表的時(shí)候?qū)?shù)據(jù)進(jìn)行了大量過濾（id=6），最后得到10條數(shù)據(jù)，耗費(fèi)了1245個(gè)邏輯讀。
第2條sql從索引中掃描了10條數(shù)據(jù)，耗費(fèi)了5個(gè)邏輯讀?？梢钥闯?，第二條sql的執(zhí)行計(jì)劃是正確的，而第一條sql的執(zhí)行計(jì)劃是錯(cuò)誤的，應(yīng)該盡量在索引掃描的時(shí)候就取得10行數(shù)據(jù)。
（為什么上面的兩條sql只有過濾條件不一樣，而第一條sql的執(zhí)行計(jì)劃就錯(cuò)了呢？這是因?yàn)榈谝粭lsql的過濾條件where owner='SCOTT'，在表中只有很少數(shù)據(jù)，通過掃描object_id列的索引，然后在回表去匹配owner='SCOTT'，因?yàn)閛wner='SCOTT'數(shù)據(jù)量很少，要搜索大量數(shù)據(jù)才能匹配上。而第二條sql的過濾條件owner='SYS'，因?yàn)閿?shù)據(jù)量多，只需要搜索少量的數(shù)據(jù)就能匹配上。）
---優(yōu)化第一條sql：（就必須讓過濾條件的列出現(xiàn)在索引中，如下：）
SQL> create index idx_test_all on t_test(owner,object_id); ---創(chuàng)建一個(gè)組合索引，包含owner字段
SQL> select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test_all) */ * from t_test where owner='SCOTT' order by object_id) a ) where rownum <=10) where rn >=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------、
SQL_ID 9mm61b7j943sf, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test_all) */ * from t_test where owner='SCOTT' order
by object_id) a ) where rownum <=10) where rn >=1
Plan hash value: 3696904346
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 6 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 6 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 6 |
| 3 | VIEW | | 1 | 12 | 10 |00:00:00.01 | 6 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 6 |
| 5 | VIEW | | 1 | 12 | 10 |00:00:00.01 | 6 |
| 6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 12 | 10 |00:00:00.01 | 6 |
|* 7 | INDEX RANGE SCAN | IDX_test_ALL | 1 | 12 | 10 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
7 - access("OWNER"='SCOTT')
Note
-----
- dynamic sampling used for this statement (level=2)
32 rows selected.（這時(shí)候會(huì)發(fā)現(xiàn)，sql走了索引范圍掃描，從索引中掃描了10條數(shù)據(jù)，一共耗費(fèi)了6個(gè)邏輯讀。這說明現(xiàn)在的執(zhí)行計(jì)劃是正確的。）
注意：
在實(shí)際的生成環(huán)境中，過濾條件一般都是綁定變量，我們無法控制傳參究竟傳入哪個(gè)值，這就不能確定返回?cái)?shù)據(jù)究竟是多還是少了，所以，建議最好將排序的列包含在索引中。
但是要注意：如果排序列有多個(gè)列，創(chuàng)建索引的時(shí)候，我們要將所有的排序列包含在索引中，并且要注意排序列先后順序，而且還要注意列時(shí)升序還是降序。如果分頁語句中排序列只有一個(gè)列，但是是降序顯示的，創(chuàng)建索引的時(shí)候就沒必要降序創(chuàng)建索引了，我們可以使用hint：index_desc 讓索引降序掃描就可以了。
例子：
(創(chuàng)建索引，只能是object_id在前，object_name在后，另外object_name是降序顯示的，那么在創(chuàng)建索引的時(shí)候，還要指定object_name列降序排序。下面的sql也沒有過濾條件，在創(chuàng)建索引的時(shí)候還要加個(gè)常量，如下所示：)
SQL> create index idx_test_2 on t_test(object_id,object_name desc,0); ---創(chuàng)建一個(gè)組合索引，包含owner字段
SQL> select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test_2) */ * from t_test order by object_id,object_name desc) a ) where rownum <=10) where rn >=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------
SQL_ID g8zgbvt1u1qjq, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test_2) */ * from t_test order by
object_id,object_name desc) a ) where rownum <=10) where rn >=1
Plan hash value: 2251915778
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 | 1 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 5 | 1 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 5 | 1 |
| 3 | VIEW | | 1 | 80700 | 10 |00:00:00.01 | 5 | 1 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 5 | 1 |
| 5 | VIEW | | 1 | 80700 | 10 |00:00:00.01 | 5 | 1 |
| 6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 80700 | 10 |00:00:00.01 | 5 | 1 |
| 7 | INDEX FULL SCAN | IDX_test_2 | 1 | 80700 | 10 |00:00:00.01 | 3 | 1 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
Note
-----
- dynamic sampling used for this statement (level=2)
31 rows selected.如果在創(chuàng)建索引的時(shí)候沒有指定 object_name列降序排序，那么執(zhí)行計(jì)劃中會(huì)出現(xiàn)sort order by。因?yàn)樗饕信判蚝头猪撜Z句中排序不一致，如:
SQL> create index idx_test_3 on t_test(object_id,object_name,0);
SQL> select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test_3) */ * from t_test order by object_id,object_name desc) a ) where rownum <=10) where rn >=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------
SQL_ID bapu5jn5vn22r, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test_3) */ * from t_test order by
object_id,object_name desc) a ) where rownum <=10) where rn >=1
Plan hash value: 3547841113
-----------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.10 | 1490 | 426 | | | |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.10 | 1490 | 426 | | | |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.10 | 1490 | 426 | | | |
| 3 | VIEW | | 1 | 80700 | 10 |00:00:00.10 | 1490 | 426 | | | |
| 4 | COUNT | | 1 | | 10 |00:00:00.10 | 1490 | 426 | | | |
| 5 | VIEW | | 1 | 80700 | 10 |00:00:00.10 | 1490 | 426 | | | |
| 6 | SORT ORDER BY | | 1 | 80700 | 10 |00:00:00.10 | 1490 | 426 | 10M| 1272K| 9811K (0)|
| 7 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 80700 | 71903 |00:00:00.06 | 1490 | 426 | | | |
| 8 | INDEX FULL SCAN | IDX_test_3 | 1 | 80700 | 71903 |00:00:00.03 | 427 | 426 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
Note
-----
- dynamic sampling used for this statement (level=2)
32 rows selected.(從上面可以看出，執(zhí)行計(jì)劃中出現(xiàn)了 sort order by，這就意味著分頁語句沒有利用到索引已經(jīng)排序的特性，執(zhí)行計(jì)劃是錯(cuò)誤的，這時(shí)候就需要?jiǎng)?chuàng)建正確的索引。)
例子3：
一條sql（，過濾條件有等值條件，也有非等值條件，當(dāng)然也有order by），如下，將下面的sql分頁查詢：
select * from t_test where owner='SYS' and object_id > 1000 order by object_name;
--：創(chuàng)建索引（因?yàn)閛wner是等值過濾，object_Id是非等值過濾，創(chuàng)建索引的時(shí)候要優(yōu)先將等值過濾列和排序列組合在一起，然后再將非等值過濾列放到后面）如下：
create index idx_test_4 on t_test(owner,object_name,object_id);
select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test_4) */ * from t_test where owner='SYS' and object_id > 1000 order by object_name) a ) where rownum <=10) where rn >=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------:
SQL_ID 4z6tjgrdjm5a1, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test_4) */ * from t_test where owner='SYS' and
object_id > 1000 order by object_name) a ) where rownum <=10) where rn
>=1
Plan hash value: 1432357471
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 14 | 2 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 14 | 2 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 14 | 2 |
| 3 | VIEW | | 1 | 25683 | 10 |00:00:00.01 | 14 | 2 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 14 | 2 |
| 5 | VIEW | | 1 | 25683 | 10 |00:00:00.01 | 14 | 2 |
| 6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 25683 | 10 |00:00:00.01 | 14 | 2 |
|* 7 | INDEX RANGE SCAN | IDX_test_4 | 1 | 256 | 10 |00:00:00.01 | 4 | 2 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
7 - access("OWNER"='SYS' AND "OBJECT_ID">1000)
filter("OBJECT_ID">1000)
Note
-----
- dynamic sampling used for this statement (level=2)
34 rows selected.（可以看出，執(zhí)行計(jì)劃中沒有出現(xiàn) sort order by，邏輯讀也只有14個(gè)，說明執(zhí)行計(jì)劃是正確的。）
注意：
①：如果分頁語句中有排序（order by），要利用索引已經(jīng)排序的特性，將order by的列按照排序的先后順序包含在索引中，同時(shí)要注意排序是升序還是降序。如果分頁語句中有過濾條件，我們要注意過濾條件是否有等值過濾條件，如果有等值過濾條件，要將等值過濾條件優(yōu)先組合在一起，然后將排序列放在等值過濾條件后面，最后將非等值過濾列放排序列后面。如果分頁語句中沒有等值過濾條件，我們應(yīng)該先將排序列放在索引前面，將非等值過濾列放后面，最后利用rownum的count stopkey特性來優(yōu)化分頁sql。如果分頁中沒有排序，可以直接利用rownum的count stopkey特性來優(yōu)化分頁sql。
②：要想一眼看出執(zhí)行計(jì)劃是否正確，就要先看分頁語句有沒有order by，再看執(zhí)行計(jì)劃有沒有sort order by，如果執(zhí)行計(jì)劃中有sort order by，說明執(zhí)行計(jì)劃一般是錯(cuò)誤的。
③：如果分頁語句中排序的表是分區(qū)表，這時(shí)我們要看分頁語句中是否有跨區(qū)分區(qū)掃描，如果有跨分區(qū)掃描，創(chuàng)建的索引一般都是global索引，如果不創(chuàng)建global索引，就無法保證分頁的順序與所有的順序是一致的。如果就只掃描一個(gè)分區(qū)，這時(shí)可以創(chuàng)建local索引：
例子：創(chuàng)建分區(qū)表，并導(dǎo)入數(shù)據(jù)
CREATE TABLE p_test
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30)
) partition by range (object_id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (30000),
partition p4 values less than (40000),
partition p5 values less than (50000),
partition p6 values less than (60000),
partition p7 values less than (70000),
partition px values less than (maxvalue));
SQL> insert into p_test select * from dba_objects;
71917 rows created.
---例子1：分頁語句：
select * from (selct * from (select a.*,rownum rn from (select * from p_test order by object_id) a ) where rownum <=10) where rn >=1;
（這個(gè)分頁語句沒有過濾條件，因此會(huì)掃描表中的所有分區(qū)。因?yàn)榕判蛄星『檬欠秶謪^(qū)列，范圍分區(qū)每個(gè)分區(qū)的數(shù)據(jù)也是遞增的，這時(shí)我們創(chuàng)建索引可以創(chuàng)建為local索引。但是如果將范圍分區(qū)改為list分區(qū)或者h(yuǎn)ash分區(qū)，這時(shí)我們就必須創(chuàng)建global索引，因?yàn)閘ist分區(qū)和hash分區(qū)是無序的。）
SQL> create index idx_test_id on p_test(object_id,0) local; ---創(chuàng)建local索引
select * from (select * from (select a.*,rownum rn from (select /*+ index(p_test idx_test_id ) */ * from p_test order by object_id) a ) where rownum <=10) where rn >=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL_ID bxw1059jmgxvx, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(p_test idx_test_id ) */ * from p_test order by object_id) a )
where rownum <=10) where rn >=1
Plan hash value: 1291390031
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 | 1 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 5 | 1 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 5 | 1 |
| 3 | VIEW | | 1 | 63696 | 10 |00:00:00.01 | 5 | 1 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 5 | 1 |
| 5 | VIEW | | 1 | 63696 | 10 |00:00:00.01 | 5 | 1 |
| 6 | PARTITION RANGE ALL | | 1 | 63696 | 10 |00:00:00.01 | 5 | 1 |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| P_TEST | 1 | 63696 | 10 |00:00:00.01 | 5 | 1 |
| 8 | INDEX FULL SCAN | IDX_TEST_ID | 1 | 63696 | 10 |00:00:00.01 | 3 | 1 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
Note
-----
- dynamic sampling used for this statement (level=2)
32 rows selected.例子2：分頁語句（根據(jù)object_name排序）
select * from (selct * from (select a.*,rownum rn from (select * from p_test order by object_name) a ) where rownum <=10) where rn >=1;
這時(shí)候我們需要?jiǎng)?chuàng)建global索引，因?yàn)槿绻潜镜厮饕蜁?huì)產(chǎn)生 sort order by
SQL> drop index idx_test_id ;
SQL> create index idx_test_id on p_test(object_name,0);
select * from (select * from (select a.*,rownum rn from (select /*+ index(p_test idx_test_id ) */ * from p_test order by object_name) a ) where rownum <=10) where rn >=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL_ID 25pm0f6b6m49x, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(p_test idx_test_id ) */ * from p_test order by object_name) a )
where rownum <=10) where rn >=1
Plan hash value: 246970912
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 10 | 2 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 10 | 2 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 10 | 2 |
| 3 | VIEW | | 1 | 63696 | 10 |00:00:00.01 | 10 | 2 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 10 | 2 |
| 5 | VIEW | | 1 | 63696 | 10 |00:00:00.01 | 10 | 2 |
| 6 | TABLE ACCESS BY GLOBAL INDEX ROWID| P_TEST | 1 | 63696 | 10 |00:00:00.01 | 10 | 2 |
| 7 | INDEX FULL SCAN | IDX_TEST_ID | 1 | 63696 | 10 |00:00:00.01 | 4 | 2 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
Note
-----
- dynamic sampling used for this statement (level=2)
31 rows selected.免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼