一次簡單的分頁優化
今天對一個排序分頁查詢進行了一次優化，優化後效能得到明顯改觀。下面用例子說明優化步驟。
1、首先建立測試資料
SQL> create table t as select * from dba_objects;
Table created
SQL> insert into t select * from t;
12704 rows inserted
SQL> commit;
Commit complete
SQL> insert into t select * from t;
25408 rows inserted
SQL> insert into t select * from t;
50816 rows inserted
SQL> commit;
SQL> create index idx_t_object_name on t(object_name);
Index created
SQL> ANALYZE TABLE T COMPUTE STATISTICS;
Table analyzed
2、取第一頁資料
suk@ORA10G> SELECT *
2 FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM T ORDER BY OBJECT_NAME DESC) A WHERE ROWNUM <
16)
3 WHERE RN >=1;
已選擇15行。
已用時間: 00: 00: 00.21
執行計劃
----------------------------------------------------------
Plan hash value: 882605040
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 2850 | 308 (1)| 00:00:04 |
|* 1 | VIEW | | 15 | 2850 | 308 (1)| 00:00:04 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 101K| 17M| 308 (1)| 00:00:04 |
|* 4 | SORT ORDER BY STOPKEY| | 101K| 7940K| 308 (1)| 00:00:04 |
| 5 | TABLE ACCESS FULL | T | 101K| 7940K| 308 (1)| 00:00:04 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">0)
2 - filter(ROWNUM<16)
4 - filter(ROWNUM<16)
統計資訊
----------------------------------------------------------
189 recursive calls
0 db block gets
1363 consistent gets
0 physical reads
0 redo size
1440 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
15 rows processed
從執行計劃可以看出，oracle選擇了全表掃描，雖然有stopkey，但是需要有大資料量的排序操作，效率還是很低。
我們在object_name上已經建立了索引，如果走索引，避免了排序操作，並且只返回了少量資料，效率肯定很高。現在問題的關鍵是分析為什麼沒有用索引。
先看看錶結構：
SQL> desc t
Name Type Nullable Default Comments
-------------- ------------- -------- ------- --------
OWNER VARCHAR2(30) Y
OBJECT_NAME VARCHAR2(128) Y
SUBOBJECT_NAME VARCHAR2(30) Y
OBJECT_ID NUMBER Y
DATA_OBJECT_ID NUMBER Y
OBJECT_TYPE VARCHAR2(19) Y
CREATED DATE Y
LAST_DDL_TIME DATE Y
TIMESTAMP VARCHAR2(19) Y
STATUS VARCHAR2(7) Y
TEMPORARY VARCHAR2(1) Y
GENERATED VARCHAR2(1) Y
SECONDARY VARCHAR2(1) Y
原因出來了，OBJECT_NAME欄位定義為可以為空的，因為btree索引不會索引空值，而預設情況下，oracle排序時會把空值認為無窮大。
在這裡例子中，oracle無法知道OBJECT_NAME是否存在空值，所以，oracle會選擇全表掃描。
如果確認資料當前及以後不會存在空值，把欄位屬性改成非空就可以使oracle使用索引了。
SQL> alter table t modify object_name not null;
Table altered
3、再次查詢第一頁
suk@ORA10G> SELECT *
2 FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM T ORDER BY OBJECT_NAME DESC) A WHERE ROWNUM <
16)
3 WHERE RN >=1;
已選擇15行。
已用時間: 00: 00: 00.06
執行計劃
----------------------------------------------------------
Plan hash value: 3229612337
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 2850 | 14 (0)| 00:00:01 |
|* 1 | VIEW | | 15 | 2850 | 14 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 16 | 2832 | 14 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 101K| 7940K| 14 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN DESCENDING| IDX_T_OBJECT_NAME | 16 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">0)
2 - filter(ROWNUM<16)
統計資訊
----------------------------------------------------------
201 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
1440 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
15 rows processed
可以看到，oracle正確使用了索引，效率也提升了很多。
4、取靠後的記錄
我們知道，用以上方法在返回較靠前的記錄時效率很高，但是如果返回靠後的資料，效率就有很大問題了：
suk@ORA10G> SELECT *
2 FROM (SELECT A.*, ROWNUM RN
3 FROM (SELECT * FROM T ORDER BY OBJECT_NAME DESC) A
4 WHERE ROWNUM < 30000)
5 WHERE RN >= 30000 - 15;
已選擇14行。
已用時間: 00: 00: 00.29
執行計劃
----------------------------------------------------------
Plan hash value: 882605040
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29999 | 5566K| 308 (1)| 00:00:04 |
|* 1 | VIEW | | 29999 | 5566K| 308 (1)| 00:00:04 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 101K| 17M| 308 (1)| 00:00:04 |
|* 4 | SORT ORDER BY STOPKEY| | 101K| 7940K| 308 (1)| 00:00:04 |
| 5 | TABLE ACCESS FULL | T | 101K| 7940K| 308 (1)| 00:00:04 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">29985)
2 - filter(ROWNUM<30000)
4 - filter(ROWNUM<30000)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
1338 consistent gets
0 physical reads
0 redo size
1499 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15 rows processed
從執行計劃可以看到，如果取靠後的記錄，oracle甚至放棄了索引掃描，因為成本太高了。
像這種情況，還有一種解決方法：
suk@ORA10G> SELECT T.*
2 FROM T
3 WHERE ROWID IN (SELECT RID
4 FROM (SELECT A.RID, ROWNUM RN
5 FROM (SELECT ROWID RID FROM T ORDER BY OBJECT_NAME DESC) A
6 WHERE ROWNUM < 30000)
7 WHERE RN >= 30000 - 15);
已選擇15行。
已用時間: 00: 00: 00.07
執行計劃
----------------------------------------------------------
Plan hash value: 695960827
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 103 | 550 (1)| 00:00:07 |
| 1 | NESTED LOOPS | | 1 | 103 | 550 (1)| 00:00:07 |
| 2 | VIEW | VW_NSO_1 | 29999 | 351K| 408 (1)| 00:00:05 |
| 3 | HASH UNIQUE | | 1 | 732K| | |
|* 4 | VIEW | | 29999 | 732K| 408 (1)| 00:00:05 |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | VIEW | | 101K| 1191K| 408 (1)| 00:00:05 |
| 7 | INDEX FULL SCAN DESCENDING| IDX_T_OBJECT_NAME | 101K| 2878K| 408 (1)| 00:00:05 |
| 8 | TABLE ACCESS BY USER ROWID | T | 1 | 91 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("RN">=29985)
5 - filter(ROWNUM<30000)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
138 consistent gets
0 physical reads
0 redo size
1480 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed
這種方法是先取到當前頁需要的rowid，然後再回表掃描。它和上面的方法區別在於：原來的方法是先取資料，再分頁；這種方法是先分頁，再取資料。
最主要的差別在於排序原來的方法需要排序；後一種方法不需要排序。
用最後一種介紹的方法，無論是取靠前的頁數還是靠後的頁數，效能都比原來的高。但是最後一種方法有一個前提：
1) 只返回單個表的欄位
2) 排序欄位和返回的欄位屬於同一張表
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/231499/viewspace-243991/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 記一次分頁優化優化
- 一次分頁查詢的優化優化
- 一次簡單的程式碼優化優化
- SQL優化案例-單表分頁語句的優化（八）SQL優化
- Oracle優化案例-單表分頁語句的優化（八）Oracle優化
- SQL優化：limit分頁優化SQL優化MIT
- React.js 一次動畫效能的簡單優化ReactJS動畫優化
- 優化MySQL中的分頁優化MySql
- 優化 MySQL 中的分頁優化MySql
- php 分頁 分頁類 簡單實用PHP
- 簡單的Repeater分頁程式碼
- 分頁查詢優化優化
- 簡單的儲存過程分頁儲存過程
- 簡單的分頁儲存過程儲存過程
- MySQL分頁查詢優化MySql優化
- MySQL分頁效能優化指南MySql優化
- oracle 分頁優化（stopkey）Oracle優化TopK
- MySQL單表百萬資料記錄分頁效能優化MySql優化
- react 簡單優化React優化
- msyql 簡單的sql優化SQL優化
- asp.net 簡單分頁列印ASP.NET
- SQL Server的分頁優化及Row_Number()分頁存在的問題SQLServer優化
- 關於分頁查詢的優化思路優化
- 如何寫一個簡單的分頁元件（原理）元件
- django 網站實現簡單分頁Django網站
- MySQL分優化之超大頁查詢MySql優化
- Web 頁面優化專項 > Lighthouse > 效能分數優化Web優化
- MySQL幾個簡單SQL的優化MySql優化
- 10種簡單的Java效能優化Java優化
- 簡單說兩句 Like 的優化優化
- 簡單優化容器服務優化
- nginx部署及簡單優化Nginx優化
- greenplum 簡單sql優化案例SQL優化
- SPA單頁面應用、前後端分離專案SEO優化的方法後端優化
- Android分頁元件Paging簡單使用Android元件
- 簡單封裝分頁功能pageView.js封裝ViewJS
- 資料庫分頁；簡單整理測試資料庫
- 最新+最簡單Oracle分頁查詢法Oracle