oracle下一條SQL語句的優化過程(比較詳細)
2024-08-29 13:56:07
供稿：網友
原來的語句是這樣的：
select sum(sl0000) from xstfxps2 where
dhao00 in (
select dhao00 from xstfxps1 where trunc(ywrq00)=trunc(sysdate)
and khdm00='500000003913');
已用時間: 00: 02: 49.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'XSTFXPS2'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'XSTFXPS1'
5 4 INDEX (UNIQUE SCAN) OF 'XSTFXPS1_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17355138 consistent gets
34141 physical reads
2912 redo size
198 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我們看到統計信息里面進行了17355138次邏輯讀，34141次物理IO，這是相當嚇人的數字。在執行計劃里面我們看到表XSTFXPS2來了一次全表掃描。
我們首先看一下這兩張表總的數據量：
SQL> select count(*) from xstfxps2;
COUNT(*)
----------
5585018
我們這里看到XSTFXPS2這張表有5585018條記錄。
SQL> select count(*) from xstfxps1;
COUNT(*)
----------
702121
兩張表的表結構如下所示：
SQL> desc xstfxps1
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DHAO00 NUMBER(8)
LHDH00 NUMBER(8) Y
FLDH00 NUMBER(8) Y
FPLB00 VARCHAR2(2) Y
YWRQ00 DATE Y
YWRY00 VARCHAR2(8) Y
SHRQ00 DATE Y
XSQRRQ DATE Y
XSQRRY VARCHAR2(8) Y
KHDM00 VARCHAR2(12)
XKZH00 VARCHAR2(12)
CKDM00 VARCHAR2(2) Y
THCKDM VARCHAR2(2) Y
XSFSDM VARCHAR2(2) Y
FXRYDM VARCHAR2(4) Y
SHRYDM VARCHAR2(4) Y
SHBJ00 VARCHAR2(1) 'N'
FXBJ00 VARCHAR2(1) 'N'
SKBJ00 VARCHAR2(2) Y
FKDM00 VARCHAR2(2) Y
SQL> desc xstfxps2
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DHAO00 NUMBER(8)
SPDM00 VARCHAR2(8)
DJIA00 NUMBER(7,2) 0
FXSL00 NUMBER Y 0
SL0000 NUMBER Y 0
THSL00 NUMBER Y 0
JE0000 NUMBER Y 0
SE0000 NUMBER Y
FPBBH0 VARCHAR2(11) Y
FPHAO0 VARCHAR2(10) Y
RBDH00 NUMBER(8) Y
其中XSTFXPS1的客戶訂單的表頭，保存訂單的客戶信息、訂貨日期等信息。XSTFXPS2是訂單的表體，詳細記錄了客戶訂單的商品、價格、數量等信息。
調整的第一步是把子查詢提取出來，再看語句的執行計劃。通常來說，如果語句能夠避免子查詢的使用，就盡量不用子查詢。因為子查詢的開銷是相當昂貴的。改寫后的語句如下：
select sum(sl0000)
from xstfxps2 a,(select dhao00 from xstfxps1 where trunc(ywrq00)=trunc(sysdate)
and khdm00='500000003913') b
where a.dhao00=b.dhao00;
已用時間: 00: 00: 03.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'XSTFXPS2'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'XSTFXPS1'
5 3 INDEX (RANGE SCAN) OF 'XSTFXPS2_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11974 consistent gets
225 physical reads
832 redo size
211 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我們可以看到邏輯IO由原來的17355138次下降到11974次，有了數量級的提升。執行時間也有原來將近3分鐘下降到現在的3秒多一些。很顯然性能有了大幅的提升。不過我們看到執行計劃里面表XSTFXPS1還是有一個全表掃描存在。通常來說我們應該盡量避免全表掃描的存在，尤其對于大表，應該建立合適的索引以避免FTS的產生。我們來看這兩張表的索引信息：
select index_name,column_name from dba_ind_columns where table_name like 'XSTFXPS%'
INDEX_NAME COLUMN_NAME
------------------------------ -----------------------------------
XSTFXPS1_PK DHAO00
XSTFXPS2_PK DHAO00
XSTFXPS2_PK SPDM00
我們看到這兩張表除了主鍵約束外都沒有建另外的索引。根據語句的查詢情況，我們建立了如下的復合索引：
create index idx_xstfxps1_khdm00_ywrq00 on xstfxps1(khdm00,ywrq00) tablespace indx;
為了使用索引，我們必須對原來的日期字段的條件進行一些調整。因為有個trunc()函數的存在，語句將不會使用到索引。我們只要明白trunc(ywrq00)=trunc(sysdate)事實上等同于ywrq00大于trunc(sysdate)，小于trunc(sysdate+1)減去一秒，我們就有了比較好的辦法來處理
這個條件。最終改寫后的語句如下：
select sum(sl0000)
from xstfxps2 a, xstfxps1 b
where a.dhao00=b.dhao00
and b.khdm00='500000003913'
and b.ywrq00 between trunc(sysdate)
and trunc(sysdate)+1-1/(24*60*60);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'XSTFXPS2'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'XSTFXPS1'
5 4 INDEX (RANGE SCAN) OF 'IDX_XSTFXPS1_KHDM00_YWRQ00'
(NON-UNIQUE)
6 3 INDEX (RANGE SCAN) OF 'XSTFXPS2_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
210 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我們這時候看邏輯IO已經降為3次，語句的執行計劃也符合我們的調整目標，創建的索引產生了比較大的效果。這條語句的調整至此告一段落。
