ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sql优化用group by 函数代替分析函数

sql优化用group by 函数代替分析函数

原创 Linux操作系统 作者:paulyibinyi 时间:2008-03-21 15:17:25 0 删除 编辑

SQL> Select dfId,
  2         Id As Client_Id,
  3         Login As Client_Name,
  4         To_Char(Created_Date, 'yyyy-mm-dd') As Start_Date,
  5         Sum(1) Over() As Total_Count
  6    From (Select a.*
  7            From tb_test1 a,
  8                 (Select Client_Id
  9                    From (Select Client_Id,
 10                                 Event_Date,
 11                                 Row_Number() Over(Partition By Client_Id Ord
er By Event_Date Desc) Rn
 12                            From tb_test1_Rolling_Daily_Repo)
 13                   Where Rn = 1
 14                     And Event_Date <
 15                         (Select Max(Event_Date) From tb_test1_abc_Repo
rt)) b
 16           Where a.Id = b.Client_Id
 17             And a.dfId = 4
 18          Union
 19          Select *
 20            From tb_test1 a
 21           Where a.dfId = 4
 22             And a.Created_Date <
 23                 (Select Max(To_Date) From tb_test1_abc_Report) - 1
 24             And a.Id Not In
 25                 (Select Distinct Client_Id From tb_test1_Rolling_Daily_Repo
))
 26   Order By Login;

3185 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=24 Card=171 Bytes=10
          602)

   1    0   WINDOW (SORT) (Cost=24 Card=171 Bytes=10602)
   2    1     VIEW (Cost=24 Card=171 Bytes=10602)
   3    2       SORT (UNIQUE) (Cost=24 Card=171 Bytes=24700)
   4    3         UNION-ALL
   5    4           HASH JOIN (Cost=10 Card=19 Bytes=3268)
   6    5             VIEW (Cost=3 Card=19 Bytes=665)
   7    6               WINDOW (SORT PUSHED RANK) (Cost=3 Card=19 Byte
          s=228)

   8    7                 INDEX (FULL SCAN) OF 'IDX_CRDR_CLIENT_GAME_E
          VENT' (UNIQUE) (Cost=1 Card=19 Bytes=228)

   9    6               SORT (AGGREGATE)
  10    9                 INDEX (FULL SCAN (MIN/MAX)) OF 'FKINDEX5_711
          ' (NON-UNIQUE) (Cost=3 Card=2156978 Bytes=17255824)

  11    5             TABLE ACCESS (FULL) OF 'tb_test1' (Cost=6 Card=
          3189 Bytes=436893)

  12    4           HASH JOIN (ANTI) (Cost=8 Card=152 Bytes=21432)
  13   12             TABLE ACCESS (FULL) OF 'tb_test1' (Cost=6 Card=
          159 Bytes=21783)

  14   13               SORT (AGGREGATE)
  15   14                 INDEX (FULL SCAN (MIN/MAX)) OF 'FKINDEX4_101
          ' (NON-UNIQUE) (Cost=3 Card=2156978 Bytes=17255824)

  16   12             INDEX (FULL SCAN) OF 'IDX_CRDR_CLIENT_GAME_EVENT
          ' (UNIQUE) (Cost=1 Card=19 Bytes=76)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        268  consistent gets
          0  physical reads
          0  redo size
      73882  bytes sent via SQL*Net to client
       2828  bytes received via SQL*Net from client
        214  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       3185  rows processed

用row_number() over 分析函数一致性读为268

SQL> Select dfId,
  2         Id As Client_Id,
  3         Login As Client_Name,
  4         To_Char(Created_Date, 'yyyy-mm-dd') As Start_Date,
  5         Sum(1) Over() As Total_Count
  6    From (Select a.*
  7            From tb_test1 a,
  8                 (select client_id
  9                    from (Select Client_Id, max(Event_Date) event_date
 10                            From tb_test1_Rolling_Daily_Repo
 11                           group by Client_Id)
 12                   where Event_Date <
 13                         (Select Max(Event_Date) From tb_test1_abc_Repo
rt)) b
 14           Where a.Id = b.Client_Id
 15             And a.dfId = 4
 16          Union
 17          Select *
 18            From tb_test1 a
 19           Where a.dfId = 4
 20             And a.Created_Date <
 21                 (Select Max(To_Date) From tb_test1_abc_Report) - 1
 22             And a.Id Not In
 23                 (Select Distinct Client_Id From tb_test1_Rolling_Daily_Repo
))
 24   Order By Login;

3185 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=17 Card=153 Bytes=94
          86)

   1    0   WINDOW (SORT) (Cost=17 Card=153 Bytes=9486)
   2    1     VIEW (Cost=17 Card=153 Bytes=9486)
   3    2       SORT (UNIQUE) (Cost=17 Card=153 Bytes=21582)
   4    3         UNION-ALL
   5    4           NESTED LOOPS (Cost=2 Card=1 Bytes=150)
   6    5             VIEW (Cost=1 Card=1 Bytes=13)
   7    6               FILTER
   8    7                 SORT (GROUP BY) (Cost=1 Card=1 Bytes=12)
   9    8                   INDEX (FULL SCAN) OF 'IDX_CRDR_CLIENT_GAME
          _EVENT' (UNIQUE) (Cost=1 Card=19 Bytes=228)

  10    7                 SORT (AGGREGATE)
  11   10                   INDEX (FULL SCAN (MIN/MAX)) OF 'FKINDEX5_7
          11' (NON-UNIQUE) (Cost=3 Card=2156978 Bytes=17255824)

  12    5             TABLE ACCESS (BY INDEX ROWID) OF 'tb_test1' (Co
          st=1 Card=1 Bytes=137)

  13   12               INDEX (UNIQUE SCAN) OF 'PK_tb_test1' (UNIQUE)
  14    4           HASH JOIN (ANTI) (Cost=8 Card=152 Bytes=21432)
  15   14             TABLE ACCESS (FULL) OF 'tb_test1' (Cost=6 Card=
          159 Bytes=21783)

  16   15               SORT (AGGREGATE)
  17   16                 INDEX (FULL SCAN (MIN/MAX)) OF 'FKINDEX4_101
          ' (NON-UNIQUE) (Cost=3 Card=2156978 Bytes=17255824)

  18   14             INDEX (FULL SCAN) OF 'IDX_CRDR_CLIENT_GAME_EVENT
          ' (UNIQUE) (Cost=1 Card=19 Bytes=76)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        159  consistent gets
          0  physical reads
          0  redo size
      73882  bytes sent via SQL*Net to client
       2828  bytes received via SQL*Net from client
        214  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       3185  rows processed

SQL>

用group by 函数一致性读为 159  减少了268-159=109 个一致性读

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

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

注册时间:2007-12-11

  • 博文量
    902
  • 访问量
    6506455