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

SQL> Select dfId,ITPUB个人空间B(K[)H$n3l8q
  2         Id As Client_Id,
fv%O"CED.p0  3         Login As Client_Name,ITPUB个人空间e#G8swRS\9I.^
  4         To_Char(Created_Date, 'yyyy-mm-dd') As Start_Date,
/}!@'QRn.`0  5         Sum(1) Over() As Total_Count
*NaVX*CG/v3U0  6    From (Select a.*ITPUB个人空间c(ah.L0a'n-H
  7            From tb_test1 a,
%qE;KPs*["n5D0  8                 (Select Client_IdITPUB个人空间EA@4Pa `W+fY_0oQ
  9                    From (Select Client_Id,ITPUB个人空间x~j pv
 10                                 Event_Date,ITPUB个人空间,D_"P{*b"Q
 11                                Row_Number() Over(Partition By Client_Id OrdITPUB个人空间~Ho%w&^
er By Event_Date Desc) Rn
:s Y)vHG"z9K#l0 12                            From tb_test1_Rolling_Daily_Repo)ITPUB个人空间4VhLma(uy5X5JlY
 13                   Where Rn = 1
%yNH eC*UC0 14                     And Event_Date <
@:u4}+xdDc,V:g G0 15                         (Select Max(Event_Date) From tb_test1_abc_RepoITPUB个人空间c7?4PT9a)ix
rt)) b
/J0x7r,@ZU#P&K0 16           Where a.Id = b.Client_Id
;xOJb*N0 17             And a.dfId = 4ITPUB个人空间(cHodu6cy
 18          UnionITPUB个人空间M{v7X ohcQ
 19          Select *ITPUB个人空间-z.A d#C ?zt
 20            From tb_test1 aITPUB个人空间m)zFg)WvZ'u2i-| T
 21           Where a.dfId = 4
P\:U-pZ Dl6r@0 22             And a.Created_Date <ITPUB个人空间k0hQL0DX&b
 23                 (Select Max(To_Date) From tb_test1_abc_Report) - 1ITPUB个人空间m MbVG
 24             And a.Id Not In
o6]2_#q'?PT0 25                 (Select Distinct Client_Id From tb_test1_Rolling_Daily_Repo
-Mvx-d3^s0))
W_a-P'k0Y._*E?0 26   Order By Login;

3185 rows selected.

jJp1lXh G.s8X0Execution PlanITPUB个人空间~"jq'C7\],Y+s+uh-v
----------------------------------------------------------ITPUB个人空间"Ftxt/x"J
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=24 Card=171 Bytes=10
] yBbN*\0          602)

   1    0   WINDOW (SORT) (Cost=24 Card=171 Bytes=10602)ITPUB个人空间yWa.zo_
   2    1     VIEW (Cost=24 Card=171 Bytes=10602)ITPUB个人空间E
nIG!G-PJGee
   3    2       SORT (UNIQUE) (Cost=24 Card=171 Bytes=24700)ITPUB个人空间:uf6\SI.N
   4    3         UNION-ALLITPUB个人空间'Ar%dH4coe.g
   5    4           HASH JOIN (Cost=10 Card=19 Bytes=3268)

}#NjeL/NdO8}0   6    5             VIEW (Cost=3 Card=19 Bytes=665)ITPUB个人空间n;\UC;uDk
   7    6               WINDOW (SORT PUSHED RANK) (Cost=3 Card=19 Byte
d~ F_i1m9h0          s=228)

   8    7                 INDEX (FULL SCAN) OF 'IDX_CRDR_CLIENT_GAME_E
j+G"J;U)nUY-n U0          VENT' (UNIQUE) (Cost=1 Card=19 Bytes=228)

   9    6               SORT (AGGREGATE)
?p-eoGj0  10    9                 INDEX (FULL SCAN (MIN/MAX)) OF 'FKINDEX5_711ITPUB个人空间*]3b%Lx1yK(nG
          ' (NON-UNIQUE) (Cost=3 Card=2156978 Bytes=17255824)

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

  12    4           HASH JOIN (ANTI) (Cost=8 Card=152 Bytes=21432)
G:mQ4hI0a p!f_9o0  13   12             TABLE ACCESS (FULL) OF 'tb_test1' (Cost=6 Card=
L ]b1J%fF"m0          159 Bytes=21783)

  14   13               SORT (AGGREGATE)
s9U.w7p\'l-m0  15   14                 INDEX (FULL SCAN (MIN/MAX)) OF 'FKINDEX4_101
0fy){;AJK/EZ0          ' (NON-UNIQUE) (Cost=3 Card=2156978 Bytes=17255824)

  16   12             INDEX (FULL SCAN) OF 'IDX_CRDR_CLIENT_GAME_EVENTITPUB个人空间b]'m#gb.k
          ' (UNIQUE) (Cost=1 Card=19 Bytes=76)

Statistics
~+Ws[J@MtK0----------------------------------------------------------
$`F,h}S0          0  recursive calls
g hc2@J!p+` Z0          0  db block gets
l^z Qc!DQ0        268  consistent getsITPUB个人空间W^*Z&Z
k7H _)[
          0  physical readsITPUB个人空间k5a]&VV(?9AP
          0  redo sizeITPUB个人空间F iWG
}$g
      73882  bytes sent via SQL*Net to client
/J I(e%s]6b1qr$j0       2828  bytes received via SQL*Net from clientITPUB个人空间7Pr"y)C4D7a+r
        214  SQL*Net roundtrips to/from client
0QIo8i|&M9uC3o0          3  sorts (memory)ITPUB个人空间hI]ih|
          0  sorts (disk)
$C7H&Gx X'x0       3185  rows processed

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

SQL> Select dfId,
+dEJ!a5gXS5y0  2         Id As Client_Id,
R"Cjl1qCG6~I0  3         Login As Client_Name,
1|sG.fwR0  4         To_Char(Created_Date, 'yyyy-mm-dd') As Start_Date,
dQB+L?1C@'xnM0  5         Sum(1) Over() As Total_CountITPUB个人空间"m0O
HK mEuR9}L
  6    From (Select a.*ITPUB个人空间$wh"J:s6K
  7            From tb_test1 a,ITPUB个人空间jqdo?-J
E {o"v~T
  8                 (select client_idITPUB个人空间LP5QZo
  9                    from (Select Client_Id, max(Event_Date) event_dateITPUB个人空间xt7Cg?NvQmE7cM
 10                            From tb_test1_Rolling_Daily_Repo
s'H7g[J{*\;OG"o9_.a0 11                           group by Client_Id)ITPUB个人空间1w'k kI6S{+HUg
 12                   where Event_Date <ITPUB个人空间2GQ-^}#D d
 13                         (Select Max(Event_Date) From tb_test1_abc_Repo
#v-jLeO0rt)) b
3~
L;r/Lk{*d0 14           Where a.Id = b.Client_IdITPUB个人空间-Ui?+Nxre
 15             And a.dfId = 4ITPUB个人空间4w:vX'p8R
Ui
 16          UnionITPUB个人空间%FGx CLL m4s
 17          Select *
7R ~9Q1Xdf0V0 18            From tb_test1 aITPUB个人空间@NF8i$tG.z1~
 19           Where a.dfId = 4ITPUB个人空间 Il/qyqU1yZt:B
 20             And a.Created_Date <ITPUB个人空间h{T.]7] s{9L
 21                 (Select Max(To_Date) From tb_test1_abc_Report) - 1ITPUB个人空间Vi {+GpeNC
 22             And a.Id Not InITPUB个人空间
e4d Er.zoyU2A8X
F
 23                 (Select Distinct Client_Id From tb_test1_Rolling_Daily_Repo
eT;qyT)me:o,jz`E0))
rT/~
D3@.mqs6MG0 24   Order By Login;

3185 rows selected.

^iFO@!j0J:K Lw4W0Execution PlanITPUB个人空间Mkfl
i,d ^Nh4^
----------------------------------------------------------
X4gJ*Fbg-x} |.C0   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=17 Card=153 Bytes=94ITPUB个人空间!d0@!K2rA a/W
          86)

   1    0   WINDOW (SORT) (Cost=17 Card=153 Bytes=9486)ITPUB个人空间.Qn }|U)p9dd0I1f
   2    1     VIEW (Cost=17 Card=153 Bytes=9486)
vS#_Nk"RVV8v6Z0   3    2       SORT (UNIQUE) (Cost=17 Card=153 Bytes=21582)ITPUB个人空间2`TQS\a-J%I
   4    3         UNION-ALLITPUB个人空间'X|Kunz9r.H sv P
   5    4           NESTED LOOPS (Cost=2 Card=1 Bytes=150)ITPUB个人空间4}zMg8Z
   6    5             VIEW (Cost=1 Card=1 Bytes=13)ITPUB个人空间S!Q ?nc
   7    6               FILTERITPUB个人空间E3s(Gz.Gn#de
   8    7                 SORT (GROUP BY) (Cost=1 Card=1 Bytes=12)ITPUB个人空间^o1_#Ykm0[oNU
   9    8                   INDEX (FULL SCAN) OF 'IDX_CRDR_CLIENT_GAMEITPUB个人空间 o,Ou;o Eel
          _EVENT' (UNIQUE) (Cost=1 Card=19 Bytes=228)

  10    7                 SORT (AGGREGATE)ITPUB个人空间s)YG2EaBp'\MA$G
  11   10                   INDEX (FULL SCAN (MIN/MAX)) OF 'FKINDEX5_7ITPUB个人空间` }g \f,z
          11' (NON-UNIQUE) (Cost=3 Card=2156978 Bytes=17255824)

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

  13   12               INDEX (UNIQUE SCAN) OF 'PK_tb_test1' (UNIQUE)
tLe!bzo DIn0  14    4           HASH JOIN (ANTI) (Cost=8 Card=152 Bytes=21432)
0{:FEen0  15   14             TABLE ACCESS (FULL) OF 'tb_test1' (Cost=6 Card=ITPUB个人空间r2P@&aM
          159 Bytes=21783)

  16   15               SORT (AGGREGATE)ITPUB个人空间2C"OR:jOc:u{
  17   16                 INDEX (FULL SCAN (MIN/MAX)) OF 'FKINDEX4_101
0N%jxPc'm0          ' (NON-UNIQUE) (Cost=3 Card=2156978 Bytes=17255824)

  18   14             INDEX (FULL SCAN) OF 'IDX_CRDR_CLIENT_GAME_EVENTITPUB个人空间zY7D0`8U+L
          ' (UNIQUE) (Cost=1 Card=19 Bytes=76)

StatisticsITPUB个人空间/G#sY_v2l6O
----------------------------------------------------------
}+g1v*az5W4z*W0          0  recursive calls
9t$\ex;r5t8C0          0  db block getsITPUB个人空间(a\G5E0@l0Z6|pR&Xo
       159  consistent gets
yp,|AV1s1g5J4n0          0  physical reads
hHLjZj z0          0  redo size
s|0WCx$n6w0      73882  bytes sent via SQL*Net to clientITPUB个人空间S%r
F%Xf[QB
       2828  bytes received via SQL*Net from clientITPUB个人空间H_(DDk g }4dg
        214  SQL*Net roundtrips to/from clientITPUB个人空间 `etz%yCm([6o8[
          3  sorts (memory)
)N,{"~
SM"T~"u0          0  sorts (disk)
_h{$G^-yib0       3185  rows processed

SQL>

用group by 函数一致性读为 159  减少了268-159=109 个一致性读
m"]*N(NT0