近日中午一開發(fā)過來說生產(chǎn)有條 SQL 執(zhí)行緩慢，讓看一下執(zhí)行計劃。測試環(huán)境說也有同樣的問題 SQL，那么則開始在測試環(huán)境搞一搞吧，排查過程大概記錄如下,對于 優(yōu)化也就是一知半解，故此只能拋磚引玉，如有錯誤還望指正。
開發(fā)發(fā)過來的分頁 SQL 如下(敏感信息均已過濾轉(zhuǎn)換)，由于篇幅問題，這里不放入 PLSQL 格式化展開了。
select "v0" "SEQUENCE_NO", "v1" "PK_DELIVER_INFO", "v2" "TRANS_NO", "v3" "AGENT_ID", "v4" "TRANS_TIME", "v5" "RST_CODE", "v6" "RST_MESS", "v7" "COUNT", "v8" "QUARTER", "v9" "QUERY_BEG_DATE", "v10" "QUERY_END_DATE", "v11" "START_INDEX", "v12" "ITEMS_COUNT", "v13" "PULL_WAY", "v14" "CREATE_TIME", "v15" "UPDATE_TIME", "v16" "DELETE_TIME" from (select "x"."v0", "x"."v1", "x"."v2", "x"."v3", "x"."v4", "x"."v5", "x"."v6", "x"."v7", "x"."v8", "x"."v9", "x"."v10", "x"."v11", "x"."v12", "x"."v13", "x"."v14", "x"."v15", "x"."v16", rownum "rn" from (select "T_ORDER_INFO"."SEQUENCE_NO" "v0", "T_ORDER_INFO"."PK_DELIVER_INFO" "v1", "T_ORDER_INFO"."TRANS_NO" "v2", "T_ORDER_INFO"."AGENT_ID" "v3", "T_ORDER_INFO"."TRANS_TIME" "v4", "T_ORDER_INFO"."RST_CODE" "v5", "T_ORDER_INFO"."RST_MESS" "v6", "T_ORDER_INFO"."COUNT" "v7", "T_ORDER_INFO"."QUARTER" "v8", "T_ORDER_INFO"."QUERY_BEG_DATE" "v9", "T_ORDER_INFO"."QUERY_END_DATE" "v10", "T_ORDER_INFO"."START_INDEX" "v11", "T_ORDER_INFO"."ITEMS_COUNT" "v12", "T_ORDER_INFO"."PULL_WAY" "v13", "T_ORDER_INFO"."CREATE_TIME" "v14", "T_ORDER_INFO"."UPDATE_TIME" "v15", "T_ORDER_INFO"."DELETE_TIME" "v16" from "T_ORDER_INFO" where (1 = 1 and "T_ORDER_INFO"."AGENT_ID" = 'C002374') order by "v10" desc, "v2" desc) "x" where rownum <= (0 + 1)) where "rn" > 0 order by "rn";
下面來一起看看吧
測試數(shù)據(jù)庫版本及補丁信息
Linux 6.10 11204 RAC SQL*Plus: Release 11.2.0.4.0
Database Patch Set Update : 11.2.0.4.190416
1、查看統(tǒng)計信息收集時間，防止統(tǒng)計信息過舊
SQL> set line 345
SQL> select TABLE_NAME,OWNER,NUM_ROWS,LAST_ANALYZED from dba_tables where table_name='T_ORDER_INFO' and owner='PROD';
TABLE_NAME OWNER NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
T_ORDER_INFO PROD 3625092 2021-10-28 17:36:25
SQL> select count(*) from PROD.T_ORDER_INFO;
COUNT(*)
----------
3625092
2、查看創(chuàng)建索引情況
如下只有三個索引
SQL> select owner,index_name,table_owner,table_name,tablespace_name,last_analyzed,status from dba_indexes where table_name='T_ORDER_INFO' and table_owner='PROD';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLESPACE_NAME LAST_ANALYZED STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------- --------
PROD T_ORDER_INFO_UK1 PROD T_ORDER_INFO PROD_DATA 2021-10-28 17:36:25 VALID
PROD T_ORDER_INFO_INDEX1 PROD T_ORDER_INFO PROD_DATA 2021-10-28 17:36:25 VALID
PROD T_ORDER_INFO_PK PROD T_ORDER_INFO PROD_DATA 2021-10-28 17:36:25 VALID
3、查看索引所在的列
序列為主鍵索引，PK_DELIVER_INFO 列為唯一索引，普通索引剛好在 AGENT_ID 我們最開始的 where 子句中。
SET LINE 234
COL INDEX_OWNER FOR A30
COL TABLE_OWNER FOR A15
COL TABLE_NAME FOR A25
COL INDEX_NAME FOR A28
COL COLUMN_NAME FOR A20
SELECT INDEX_OWNER,TABLE_OWNER,TABLE_NAME,INDEX_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='&tablename' ORDER BY INDEX_NAME;
SQL> SELECT INDEX_OWNER,TABLE_OWNER,TABLE_NAME,INDEX_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='T_ORDER_INFO' and table_owner='PROD';
INDEX_OWNER TABLE_OWNER TABLE_NAME INDEX_NAME COLUMN_NAME
------------------------------ --------------- --------------- ------------------ --------------------
PROD PROD T_ORDER_DELIVER T_ORDER_DELIVER_IN SEQUENCE_NO
_INFO FO_PK
PROD PROD T_ORDER_DELIVER T_ORDER_DELIVER_IN AGENT_ID
_INFO FO_INDEX1
PROD PROD T_ORDER_DELIVER T_ORDER_DELIVER_IN PK_DELIVER_INFO
_INFO FO_UK1
4、查看表是否分區(qū)（如下未分區(qū)）
SQL> SELECT TABLE_NAME,PARTITIONED FROM DBA_TABLES WHERE TABLE_NAME='T_ORDER_INFO' AND OWNER='PROD';
TABLE_NAME PAR
------------------------------ ---
T_ORDER_INFO NO
5、查看表的大小
SQL> select sum(bytes)/1024/1024 MB from dba_segments where OWNER='PROD' and SEGMENT_NAME ='T_ORDER_INFO';
MB
----------
768
360多萬，768M也算不上大表，對于合理的分頁查詢應(yīng)該也沒有什么問題，下面就需要看看執(zhí)行計劃了。
SQL> select TABLE_NAME,OWNER,NUM_ROWS,LAST_ANALYZED from dba_tables where table_name='T_ORDER_INFO' and owner='PROD';
TABLE_NAME OWNER NUM_ROWS LAST_ANALYZED
--------------- ------------------------------ ---------- -------------------
T_ORDER_INFO PROD 3625092 2021-11-05 16:32:11
6、首先需要拿到原 SQL 的 SQL_ID.
可以通過 awr、ash或者 v$SQL 等視圖獲取，這里通過最簡單的 v$SQL 視圖獲取。
set long 9999 line 999 pages 999
select sql_id,SQL_FULLTEXT from v$sql where sql_text like '%T_ORDER_INFO%';
5b2zcwhm267q8
SQL> @?/rdbms/admin/sqltrpt.sql
ORACLE 10g 以后提供了一個腳本 sqltrpt.sql 用來查詢最耗費資源的 SQL 語句，也可以根據(jù)輸入的SQL_ID，生成對應(yīng)執(zhí)行計劃和調(diào)優(yōu)建議，是一個不錯的調(diào)優(yōu)優(yōu)化腳本。其實是sqltrpt是SQL Tune Report的縮寫。這個腳本位于$ORACLE_HOME/rdbms/admin/sqltrpt.sql
7、查看執(zhí)行計劃
知道 sql_id 后便可以根據(jù)多種辦法查看執(zhí)行計劃。關(guān)于執(zhí)行計劃多種查看方法，可查看之前的文章鏈接 。
@?/rdbms/admin/awrsqrpt.sql
Select * from table(dbms_xplan.display_awr('5b2zcwhm267q8'));
通過 AUTOTRACE 和 PLSQL 使用 F5 查看的執(zhí)行計劃一樣。
SQL> set AUTOT TRACE
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
以上普通用戶無法使用 AUTOTRACE ，需要執(zhí)行腳本 plustrce.sql 創(chuàng)建 PLUSTRACE 角色授予普通用戶即可。
cd $ORACLE_HOME/sqlplus/admin
ll plustrce.sql
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> create role plustrace;
Role created.
SQL>
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL>
SQL> set echo off
SQL> grant PLUSTRACE to PROD;
Grant succeeded.
SQL> set autot on
SQL> conn PROD/LKKBtd7$
Connected.
SQL> set autot on
SQL>
SQL> set AUTOT TRACE
Execution Plan
----------------------------------------------------------
Plan hash value: 3374223308
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 8160 | | 4031 (1)| 00:00:49 |
| 1 | SORT ORDER BY | | 10 | 8160 | | 4031 (1)| 00:00:49 |
|* 2 | VIEW | | 10 | 8160 | | 4030 (1)| 00:00:49 |
|* 3 | COUNT STOPKEY | | | | | | |
| 4 | VIEW | | 46319 | 35M| | 4030 (1)| 00:00:49 |
|* 5 | SORT ORDER BY STOPKEY | | 46319 | 8594K| 10M| 4030 (1)| 00:00:49 |
| 6 | TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO | 46319 | 8594K| | 2111 (1)| 00:00:26 |
|* 7 | INDEX RANGE SCAN | T_ORDER_INFO_INDEX1 | 46319 | | | 469 (0)| 00:00:06 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("rn">0)
3 - filter(ROWNUM<=10)
5 - filter(ROWNUM<=10)
7 - access("T_ORDER_INFO"."AGENT_ID"='C002282')
SQL> set line 456 pages 456
SQL> Select * from table(dbms_xplan.display_cursor('5b2zcwhm267q8'));
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 1943 (100)| |
| 1 | SORT ORDER BY | | 10 | 8160 | | 1943 (1)| 00:00:24 |
|* 2 | VIEW | | 10 | 8160 | | 1942 (1)| 00:00:24 |
|* 3 | COUNT STOPKEY | | | | | | |
| 4 | VIEW | | 23300 | 17M| | 1942 (1)| 00:00:24 |
|* 5 | SORT ORDER BY STOPKEY | | 23300 | 4323K| 5336K| 1942 (1)| 00:00:24 |
| 6 | TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO | 23300 | 4323K| | 976 (1)| 00:00:12 |
|* 7 | INDEX RANGE SCAN | T_ORDER_INFO_INDEX1 | 23300 | | | 220 (0)| 00:00:03 |
------------------------------------------------------------------------------------------------------------------------
執(zhí)行計劃中有排序，而且成本 cost 也很高，autotrace 出來的達 4031。
8、優(yōu)化此 SQL
AGENT_ID 建有索引，該 SQL 也是走了此索引，但是效果不佳，那么我們嘗試創(chuàng)建一個聯(lián)合索引來看看。
create index PROD.T_ORD_INFO_IDQUERY_TRANSNO on PROD.T_ORDER_INFO(AGENT_ID,QUERY_END_DATE desc,TRANS_NO desc) tablespace PROD_INDEX online;
注意如果建立如下索引，執(zhí)行計劃則會出現(xiàn) INDEX RANGE SCAN DESCENDING，物理讀變?yōu)?3，其他基本一樣，但是使用 11 節(jié)的分頁 SQL 時執(zhí)行計劃中排序則不可避免，沒有充分利用索引有序的特性，故需刪除按照上面語法重新創(chuàng)建較好一丟丟。
create index PROD.T_ORD_INFO_IDQUERY_TRANSNO on PROD.T_ORDER_INFO(AGENT_ID,QUERY_END_DATE,TRANS_NO) tablespace PROD_INDEX online;
drop index PROD.T_ORD_INFO_IDQUERY_TRANSNO;
9、收集表統(tǒng)計信息
exec dbms_stats.gather_table_stats(ownname => 'PROD', tabname => 'T_ORDER_INFO');
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select TABLE_NAME,OWNER,NUM_ROWS,LAST_ANALYZED from dba_tables where table_name like 'T_ORDER_INFO' and owner='PROD';
10、查看執(zhí)行計劃
conn xxxx/xxxx
set autot on
Execution Plan
----------------------------------------------------------
Plan hash value: 3879506888
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 8160 | 9 (12)| 00:00:01 |
| 1 | SORT ORDER BY | | 10 | 8160 | 9 (12)| 00:00:01 |
|* 2 | VIEW | | 10 | 8160 | 8 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 11 | 8833 | 8 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO | 37872 | 7064K| 8 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T_ORD_INFO_IDQUERY_TRANSNO | 11 | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
2 physical reads
0 redo size
2765 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
原有執(zhí)行計劃中有 SORT ORDER BY 的排序操作也已經(jīng)消除了，Cost 成本值降低至 9，與原來的 4031 相比，提高了 440 多倍。那么該 SQL 還有優(yōu)化的空間嗎？
11、根據(jù)《SQL優(yōu)化核心思想》8.3 一節(jié)分頁優(yōu)化思想改寫的 SQL 如下：
select * from (select *
from (select a.*,rownum rn from (分頁SQL） a) where rownum<=10)
where rn >=1;
不知道是不是作者筆誤，多寫了一層 select 還是怎么的，根據(jù)此分頁框架改寫如下 SQL 還是達不到最優(yōu)，請繼續(xù)往下看。
select * from (select *
from (select a.*,rownum rn from
(select "T_ORDER_INFO"."SEQUENCE_NO" "v0",
"T_ORDER_INFO"."PK_DELIVER_INFO" "v1",
"T_ORDER_INFO"."TRANS_NO" "v2",
"T_ORDER_INFO"."AGENT_ID" "v3",
"T_ORDER_INFO"."TRANS_TIME" "v4",
"T_ORDER_INFO"."RST_CODE" "v5",
"T_ORDER_INFO"."RST_MESS" "v6",
"T_ORDER_INFO"."COUNT" "v7",
"T_ORDER_INFO"."QUARTER" "v8",
"T_ORDER_INFO"."QUERY_BEG_DATE" "v9",
"T_ORDER_INFO"."QUERY_END_DATE" "v10",
"T_ORDER_INFO"."START_INDEX" "v11",
"T_ORDER_INFO"."ITEMS_COUNT" "v12",
"T_ORDER_INFO"."PULL_WAY" "v13",
"T_ORDER_INFO"."CREATE_TIME" "v14",
"T_ORDER_INFO"."UPDATE_TIME" "v15",
"T_ORDER_INFO"."DELETE_TIME" "v16"
from "T_ORDER_INFO"
where ("T_ORDER_INFO"."AGENT_ID" = 'C002282')
order by "v10" desc, "v2" desc
）
a) where rownum<=10
)
where rn >=1;
執(zhí)行計劃如下：
Plan hash value: 2456897122
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 8160 | | 3162 (1)| 00:00:38 |
|* 1 | VIEW | | 10 | 8160 | | 3162 (1)| 00:00:38 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 37872 | 29M| | 3162 (1)| 00:00:38 |
| 4 | COUNT | | | | | | |
| 5 | VIEW | | 37872 | 29M| | 3162 (1)| 00:00:38 |
| 6 | SORT ORDER BY | | 37872 | 7064K| 8672K| 3162 (1)| 00:00:38 |
| 7 | TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO | 37872 | 7064K| | 1579 (1)| 00:00:19 |
|* 8 | INDEX RANGE SCAN | T_ORDER_INFO_INDEX1 | 37872 | | | 348 (0)| 00:00:05 |
-------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1173 consistent gets
0 physical reads
0 redo size
2729 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
使用原有索引 T_ORDER_INFO_INDEX1，并沒有用到新的聯(lián)合索引，原有執(zhí)行計劃中有 SORT ORDER BY 的排序操作也沒有消除了，Cost 成本值有原來的 4031 降低至 3162，效果不是很明顯.
使用 Statistics_level 看一眼真實執(zhí)行計劃也是一樣使用舊索引，還有排序操作。
grant select any dictionary to PROD;
conn PROD/PROD1245
alter session set Statistics_level=all;
執(zhí)行上述分頁 SQL
select * from table(dbms_xplan.display_cursor(null,null,‘a(chǎn)llstats last’));
那么，我們強制使用 hint 走聯(lián)合索引在看看，結(jié)果 cost 值達 13303，還是沒有達到最優(yōu)。
select /*+ index(T_ORDER_INFO T_ORD_INFO_IDQUERY_TRANSNO) */ "T_ORDER_INFO"
Execution Plan
----------------------------------------------------------
Plan hash value: 4173602263
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 8160 | 13303 (1)| 00:02:40 |
|* 1 | VIEW | | 10 | 8160 | 13303 (1)| 00:02:40 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 41284 | 32M| 13303 (1)| 00:02:40 |
| 4 | COUNT | | | | | |
| 5 | VIEW | | 41284 | 31M| 13303 (1)| 00:02:40 |
| 6 | TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO | 41284 | 7700K| 13303 (1)| 00:02:40 |
|* 7 | INDEX RANGE SCAN | T_ORD_INFO_IDQUERY_TRANSNO | 41284 | | 469 (0)| 00:00:06 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
7 - access("T_ORDER_INFO"."AGENT_ID"='C002282')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
2 physical reads
0 redo size
2729 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
12、最高效的單表分頁查詢寫法
看過老虎劉老師的最高效分頁查詢語句架構(gòu)只有三層，我們來套用看看。
select column_lists from
(select rownum as rn,A.* from
(select column_lists from table_name where col_1=:b0 order by col_2) A
where rownum<=:b2
) where rn>:b1;
改寫完 SQL 格式化如下：
select "v0",
"v1",
"v2",
"v3",
"v4",
"v5",
"v6",
"v7",
"v8",
"v9",
"v10",
"v11",
"v12",
"v13",
"v14",
"v15",
"v16"
from (select rownum as rn, A.*
from (select "T_ORDER_INFO"."SEQUENCE_NO" "v0",
"T_ORDER_INFO"."PK_DELIVER_INFO" "v1",
"T_ORDER_INFO"."TRANS_NO" "v2",
"T_ORDER_INFO"."AGENT_ID" "v3",
"T_ORDER_INFO"."TRANS_TIME" "v4",
"T_ORDER_INFO"."RST_CODE" "v5",
"T_ORDER_INFO"."RST_MESS" "v6",
"T_ORDER_INFO"."COUNT" "v7",
"T_ORDER_INFO"."QUARTER" "v8",
"T_ORDER_INFO"."QUERY_BEG_DATE" "v9",
"T_ORDER_INFO"."QUERY_END_DATE" "v10",
"T_ORDER_INFO"."START_INDEX" "v11",
"T_ORDER_INFO"."ITEMS_COUNT" "v12",
"T_ORDER_INFO"."PULL_WAY" "v13",
"T_ORDER_INFO"."CREATE_TIME" "v14",
"T_ORDER_INFO"."UPDATE_TIME" "v15",
"T_ORDER_INFO"."DELETE_TIME" "v16"
from "T_ORDER_INFO"
where "T_ORDER_INFO"."AGENT_ID" = 'C002282'
order by "v10" desc, "v2" desc) A
where rownum <= 10)
where rn > 0;
我們來看一看執(zhí)行計劃
Execution Plan
----------------------------------------------------------
Plan hash value: 2845846103
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 8160 | 8 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 8160 | 8 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 11 | 8833 | 8 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO | 37872 | 7064K| 8 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T_ORD_INFO_IDQUERY_TRANSNO | 11 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
2635 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Plan hash value: 2845846103
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 10 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 10 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 10 |
| 3 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 10 |
| 4 | TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO | 1 | 41284 | 10 |00:00:00.01 | 10 |
|* 5 | INDEX RANGE SCAN | T_ORD_INFO_IDQUERY_TRANSNO | 1 | 10 | 10 |00:00:00.01 | 5 |
-----------------------------------------------------------------------------------------------------------------------
執(zhí)行計劃中沒有排序，沒有物理讀，cost 只有 8，查詢結(jié)果秒出，這才是最優(yōu)的結(jié)果。 最后來一起看看落落大神總結(jié)的分頁優(yōu)化思路：
單表分頁語句優(yōu)化思路：如果分頁語句中有排序（order by），要利用索引已經(jīng)排序特性，將order by的列按照排序的先后順序包含在索引中，同時要注意排序是升序還是降序。如果分頁語句中有過濾條件，我們要注意過濾條件是否有等值過濾條件，如果有等值過濾條件，要將等值過濾條件優(yōu)先組合在一起，然后將排序列放在等值過濾條件后面，最后將非等值過濾列放排序列后面。如果分頁語句中沒有等值過濾條件，我們應(yīng)該先將排序列放在索引前面，將非等值過濾列放后面，最后利用rownum的COUNT STOPKEY特性來優(yōu)化分頁SQL。如果分頁中沒有過濾條件，可以將排序列和常量組合(object_name,0)創(chuàng)建索引。如果分頁中沒有排序，可以直接利用rownum的COUNT STOPKEY特性來優(yōu)化分頁SQL。
如果我們想一眼看出分頁語句執(zhí)行計劃是正確還是錯誤的，先看分頁語句有沒有ORDER BY，再看執(zhí)行計劃有沒有SORT ORDER BY，如果執(zhí)行計劃中有SORTORDER BY，執(zhí)行計劃一般都是錯誤的分頁語句中也不能有distinct、group by、max、min、avg、union、union all等關(guān)鍵字。因為當分頁語句中有這些關(guān)鍵字，我們需要等表關(guān)聯(lián)完或者數(shù)據(jù)都跑完之后再來分頁，這樣性能很差。
多表關(guān)聯(lián)分頁優(yōu)化思路:多表關(guān)聯(lián)分頁語句，如果有排序，只能對其中一個表進行排序，讓參與排序的表作為嵌套循環(huán)的驅(qū)動表，并且要控制驅(qū)動表返回的數(shù)據(jù)順序與排序的順序一致，其余表的連接列要創(chuàng)建好索引。
如果有外連接，我們只能選擇主表的列作為排序列，語句中不能有distinct、group by、max、min、avg、union、union all，執(zhí)行計劃中不能出現(xiàn)SORT ORDER BY。
——————————————————————–—–————
公眾號：JiekeXu DBA之路
墨天輪：http://m.sunline.cc/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
騰訊云：https://cloud.tencent.com/developer/user/5645107
————————————————————————----———
最后修改時間：2021-11-15 19:06:58
「喜歡這篇文章，您的關(guān)注和贊賞是給作者最好的鼓勵」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權(quán)追究責任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，墨天輪將立刻刪除相關(guān)內(nèi)容。
