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 我們看到統(tǒng)計信息里面進行了17355138次邏輯讀,34141次物理IO,這是相當嚇人的數(shù)字。在執(zhí)行計劃里面我們看到表XSTFXPS2來了一次全表掃描。 我們首先看一下這兩張表總的數(shù)據(jù)量: 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
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
我們看到這兩張表除了主鍵約束外都沒有建另外的索引。根據(jù)語句的查詢情況,我們建立了如下的復合索引: create index idx_xstfxps1_khdm00_ywrq00 on xstfxps1(khdm00,ywrq00) tablespace indx;
為了使用索引,我們必須對原來的日期字段的條件進行一些調(diào)整。因為有個trunc()函數(shù)的存在,語句將不會使用到索引。我們只要明白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已經(jīng)降為3次,語句的執(zhí)行計劃也符合我們的調(diào)整目標,創(chuàng)建的索引產(chǎn)生了比較大的效果。這條語句的調(diào)整至此告一段落。