oracle下一條SQL語句的最佳化過程(比較詳細)

原來的語句是這樣的：

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