ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 优化同事发过来的一个sql

优化同事发过来的一个sql

原创 Linux操作系统 作者:paulyibinyi 时间:2008-03-04 08:52:53 0 删除 编辑

SQL>set autotrace traceonly

SQL> Select distinct Id, Role_Id, Login
  2    From tb_test a,(Select Client_Id
  3            From Tb_test_Bet_Log y,
  4             (Select Distinct a.test_Deal_Log_Id
  5                    From Tb_test_Bet_Log a,
  6                     (Select Id
  7                            From tb_test
  8                           Where Exists (Select Id
  9                                    From (Select Id
 10                                            From Tb_Admin_Role
 11                                          Connect By Prior Id = Parent_Id
 12                                           Start With Id = 1) x
 13                                   Where Role_Id = x.Id)
 14                             And Lower(Login) = 'rtest5') b
 15                   Where a.Client_Id=b.id) b
 16               Where y.test_Deal_Log_Id=b.test_Deal_Log_Id
 17                 ) b
 18   Where a.Id=b.client_id
 19         ;

189 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2492 Card=4247 Bytes
          =237832)

   1    0   SORT (UNIQUE) (Cost=2492 Card=4247 Bytes=237832)
   2    1     HASH JOIN (Cost=2450 Card=4247 Bytes=237832)
   3    2       TABLE ACCESS (FULL) OF 'tb_test' (Cost=6 Card=3667 B
          ytes=62339)

   4    2       TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_test_BET_L
          OG' (Cost=10 Card=36 Bytes=324)

   5    4         NESTED LOOPS (Cost=2442 Card=4247 Bytes=165633)
   6    5           HASH JOIN (Cost=1262 Card=118 Bytes=3540)
   7    6             HASH JOIN (Cost=9 Card=1 Bytes=21)
   8    7               VIEW (Cost=2 Card=20 Bytes=80)
   9    8                 CONNECT BY (WITH FILTERING)
  10    9                   NESTED LOOPS
  11   10                     INDEX (UNIQUE SCAN) OF 'PK_TB_ADMIN_ROLE
          ' (UNIQUE) (Cost=1 Card=1 Bytes=4)

  12   10                     TABLE ACCESS (BY USER ROWID) OF 'TB_ADMI
          N_ROLE'

  13    9                   NESTED LOOPS
  14   13                     BUFFER (SORT)
  15   14                       CONNECT BY PUMP
  16   13                     INDEX (RANGE SCAN) OF 'INX_ADMIN_ROLE_P'
           (NON-UNIQUE) (Cost=2 Card=20 Bytes=160)

  17    7               TABLE ACCESS (FULL) OF 'tb_test' (Cost=6 Car
          d=37 Bytes=629)

  18    6             PARTITION RANGE (ALL)
  19   18               TABLE ACCESS (FULL) OF 'TB_test_BET_LOG' (Cost
          =1246
Card=2316228 Bytes=20846052)

  20    5           INDEX (RANGE SCAN) OF 'IDX_test_DEAL_LOG1' (NON-UN
          IQUE) (Cost=2 Card=36)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      39295  consistent gets
      33639  physical reads

          0  redo size
       4667  bytes sent via SQL*Net to client
        628  bytes received via SQL*Net from client
         14  SQL*Net roundtrips to/from client
         16  sorts (memory)
          0  sorts (disk)
        189  rows processed

通过红色部分可以看出逻辑读和物理读比较大,并且还有两个表全表扫描,

tb_test这个表是小表,全表扫描,但tb_test_bet_log是大表几千万数据,全表

扫描那就不合适了,这个表也没加上任何限制条件,根据业务相结合,可以加上时间限制条件

这个表是分区表,而且是按时间分区索引的,所以加上时间限制条件后

看下面优化后的语句

SQL> Select distinct Id, Role_Id, Login
  2    From tb_test a,(Select Client_Id
  3            From Tb_test_Bet_Log y,
  4             (Select Distinct a.test_Deal_Log_Id,created_date
  5                    From Tb_test_Bet_Log a,   (Select Id
  6                            From tb_test
  7                           Where Exists (Select Id
  8                                    From (Select Id
  9                                            From Tb_Admin_Role
 10                                          Connect By Prior Id = Parent_Id
 11                                           Start With Id = 1) x
 12                                   Where Role_Id = x.Id)
 13                             And Lower(Login) = 'rtest5') b
 14                   Where a.Client_Id=b.id
 15                   and a.created_date between
 16                  to_date('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
 17                 and to_date('2007-01-03 23:59:59','yyyy-mm-dd hh24:mi:ss'))
b
 18               Where y.test_Deal_Log_Id=b.test_Deal_Log_Id
 19                 ) b
 20   Where a.Id=b.client_id;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=36 Card=6 Bytes=384)
   1    0   SORT (UNIQUE) (Cost=36 Card=6 Bytes=384)
   2    1     NESTED LOOPS (Cost=34 Card=6 Bytes=384)
   3    2       NESTED LOOPS (Cost=28 Card=6 Bytes=282)
   4    3         NESTED LOOPS (Cost=18 Card=1 Bytes=38)
   5    4           HASH JOIN (Cost=9 Card=1 Bytes=21)
   6    5             VIEW (Cost=2 Card=20 Bytes=80)
   7    6               FILTER
   8    7                 CONNECT BY (WITH FILTERING)
   9    8                   NESTED LOOPS
  10    9                     INDEX (UNIQUE SCAN) OF 'PK_TB_ADMIN_ROLE
          ' (UNIQUE) (Cost=1 Card=1 Bytes=4)

  11    9                     TABLE ACCESS (BY USER ROWID) OF 'TB_ADMI
          N_ROLE'

  12    8                   NESTED LOOPS
  13   12                     BUFFER (SORT)
  14   13                       CONNECT BY PUMP
  15   12                     INDEX (RANGE SCAN) OF 'INX_ADMIN_ROLE_P'
           (NON-UNIQUE) (Cost=2 Card=20 Bytes=160)

  16    5             TABLE ACCESS (FULL) OF 'tb_test' (Cost=6 Card=
          37 Bytes=629)

  17    4           TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TB_test_BE
          T_LOG' (Cost=9 Card=2 Bytes=34)

  18   17             INDEX (RANGE SCAN) OF 'IDX_test_BET_LOG_CREATED_
          DATE' (NON-UNIQUE) (Cost=1 Card=82)

  19    3         TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_test_BET
          _LOG' (Cost=10 Card=36 Bytes=324)

  20   19           INDEX (RANGE SCAN) OF 'IDX_test_DEAL_LOG1' (NON-UN
          IQUE) (Cost=2 Card=36)

  21    2       TABLE ACCESS (BY INDEX ROWID) OF 'tb_test' (Cost=1 C
          ard=1 Bytes=17)

  22   21         INDEX (UNIQUE SCAN) OF 'PK_tb_test' (UNIQUE)

 


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1025  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         16  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
用上索引,逻辑读和物理读都降下来了

所以在优化过程中,有效的降低逻辑读和物理读那是最直接的方法而且要和业务相结合

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-199250/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
学习数据库

注册时间:2007-12-11

  • 博文量
    902
  • 访问量
    6577022