sql優化用group by 函式代替分析函式

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/，如需轉載，請註明出處，否則將追究法律責任。

sql優化用group by 函式代替分析函式

相關文章