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秒

心裡高興了一把呵呵

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

sql優化案例:改變表的寫法使代價和邏輯讀降下來

相關文章