一條SQL語句的優化過程

blue_prince發表於2004-09-07
SQL優化

              一條SQL語句的優化過程

    很簡單的一次調整，語句加了適當的索引後效能就有大幅的提升。當時看到這條語句的時候，第一感覺就是執行效率肯定低下。語句的功能是求某一客戶當天產品的總銷量。原來的語句是這樣的：

[@more@]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次，語句的執行計劃也符合我們的調整目標，建立的索引產生了比較大的效果。這條語句的調整至此告一段落。

參考連結:http://www.itpub.net/showthread.php?s=&threadid=260481

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/60857/viewspace-778340/，如需轉載，請註明出處，否則將追究法律責任。

相關文章