ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sql优化案例:改变表的写法使代价和逻辑读降下来

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

原创 Linux操作系统 作者:paulyibinyi 时间:2008-04-10 16:47:54 0 删除 编辑

优化前:

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
oracle 10g ocm oracle 10g/11g/12c ocp aix 6.1 administrator,ogg expert,ITSS 技术交流群 201703254 微信公众号 paulyibin 探讨技术,开心工作 电话 13719354869 ,深入研究数据库和开始研究big data

注册时间:2007-12-11

  • 博文量
    902
  • 访问量
    6505683