sql优化案例:改变表的写法使代价和逻辑读降下来

优化前:
SQL> set autotrace traceonly
SQL> Select a.*,b.*
2 From tb_bet_log a
3 Full Outer Join tb_user b On a.client_id = b.id
4 Where (b.AFFILIATE_CODE Is Not Null)
5 And
6 ((a.created_date Between to_date('2008-04-01 12:00:00', 'yyyy-mm-dd hh24:m
i:ss')
7 And to_date('2008-04-02 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
8 And a.game_code_id < 1000000) 9 Or b.first_login_time between to_date('2008-04-01 12:00:00','yyyy-mm-dd hh 24:mi:ss') 10 And to_date('2008-04-02 12:00:00', 'yyyy-mm-dd hh24:mi:ss') 11 Or b.first_deposit_time Between to_date('2008-04-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss') 12 And to_date('2008-04-02 12:00:00', 'yyyy-mm-dd hh24:mi:ss')); 11 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=5799 Card=2659929 By tes=2332757733) 1 0 VIEW (Cost=5799 Card=2659929 Bytes=2332757733) 2 1 UNION-ALL 3 2 FILTER 4 3 HASH JOIN (OUTER) 5 4 PARTITION RANGE (ALL) 6 5 TABLE ACCESS (FULL) OF 'TB_GAME_BET_LOG' (Cost=1 438 Card=2659928 Bytes=252693160) 7 4 TABLE ACCESS (FULL) OF 'TB_user' (Cost=6 Card=37 36 Bytes=511832) 8 2 NESTED LOOPS (ANTI) (Cost=16 Card=1 Bytes=141) 9 8 TABLE ACCESS (FULL) OF 'TB_user' (Cost=6 Card=1 By tes=137) 10 8 INDEX (RANGE SCAN) OF 'IDX_GAME_BET_CLIENT_ID1' (NON -UNIQUE) (Cost=10 Card=2659928 Bytes=10639712) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 37354 consistent gets 74871 physical reads 0 redo size 3972 bytes sent via SQL*Net to client 495 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 11 rows processed SQL>
优化后
改成每个表的数据放成一个子查询,先查出数据,再关联
SQL> select a.*, b.*
2 from (Select *
3 From tb_bet_log
4 where created_date Between
5 to_date('2008-04-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss') And
6 to_date('2008-04-02 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
7 And game_code_id < 1000000) a 8 Full Outer Join (select * 9 from tb_user 10 Where 11 first_login_time between 12 to_date('2008-04-01 12:00:00', 13 'yyyy-mm-dd hh24:mi:ss') And 14 to_date('2008-04-02 12:00:00', 15 'yyyy-mm-dd hh24:mi:ss') 16 Or first_deposit_time Between 17 to_date('2008-04-01 12:00:00', 18 'yyyy-mm-dd hh24:mi:ss') And 19 to_date('2008-04-02 12:00:00', 20 'yyyy-mm-dd hh24:mi:ss')) b On a.client_i d = b.id 21 where b.AFFILIATE_CODE is not null; 11 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=11 Card=2 Bytes=1674 ) 1 0 VIEW (Cost=11 Card=2 Bytes=1674) 2 1 UNION-ALL 3 2 FILTER 4 3 NESTED LOOPS (OUTER) 5 4 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TB_GAME_BE T_LOG' (Cost=2 Card=1 Bytes=100) 6 5 INDEX (RANGE SCAN) OF 'IDX_GAME_BET_LOG_CREATED_ DATE' (NON-UNIQUE) (Cost=1 Card=1) 7 4 VIEW PUSHED PREDICATE (Cost=1 Card=1 Bytes=137) 8 7 TABLE ACCESS (BY INDEX ROWID) OF 'TB_user' (Co st=2 Card=1 Bytes=137) 9 8 INDEX (UNIQUE SCAN) OF 'PK_TB_CLIENT' (UNIQUE) (Cost=1 Card=3736) 10 2 NESTED LOOPS (ANTI) (Cost=8 Card=1 Bytes=154) 11 10 TABLE ACCESS (FULL) OF 'TB_CLIENT' (Cost=6 Card=1 By tes=137) 12 10 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TB_GAME_BET_ LOG' (Cost=2 Card=1 Bytes=17) 13 12 INDEX (RANGE SCAN) OF 'IDX_GAME_BET_LOG_CREATED_DA TE' (NON-UNIQUE) (Cost=1 Card=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 130 consistent gets 0 physical reads 0 redo size 3972 bytes sent via SQL*Net to client 495 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 11 rows processed 从 37354 consistent gets 到 130 consistent gets 是一个很大的提高了 而且查询时间从原来的92秒到现在的0.109秒