优化同事发过来的一个sql

SQL>set autotrace traceonly

SQL> Select distinct Id, Role_Id, Login
|^xkL nm}0  2    From tb_test a,(Select Client_IdITPUB个人空间ZT4ZSs.bC%c
  3            From Tb_test_Bet_Log y,ITPUB个人空间 N,fF!uwD1m l2yT
  4             (Select Distinct a.test_Deal_Log_Id
(]0} Q6]6I]!qk0  5                    From Tb_test_Bet_Log a,
"M][*?*G"c$usp0  6                     (Select IdITPUB个人空间!lH,w9vT#r)D
  7                            From tb_test
$v&}*x l3IW0  8                           Where Exists (Select Id
.\1}|)Vk_x0  9                                    From (Select Id
-U!V!H@Ry!n0 10                                            From Tb_Admin_RoleITPUB个人空间%y {/F0k8S"Cx
 11                                          Connect By Prior Id = Parent_Id
3SCa.b;sG|DH0 12                                           Start With Id = 1) xITPUB个人空间6}_-Z i$XZ G-S
 13                                   Where Role_Id = x.Id)
)LG^(R+X@5h&W0 14                             And Lower(Login) = 'rtest5') b
:nVE'G_Z:X0 15                   Where a.Client_Id=b.id) bITPUB个人空间em!kz/|`$n
 16               Where y.test_Deal_Log_Id=b.test_Deal_Log_IdITPUB个人空间`1{m.I?m
 17                 ) bITPUB个人空间n(A
av4v'A/K
 18   Where a.Id=b.client_idITPUB个人空间'G8J*WCa
 19         ;

189 rows selected.

ITPUB个人空间qR r }w$uoI
Execution PlanITPUB个人空间O'}!n c|ZQ9EP
----------------------------------------------------------ITPUB个人空间%JS-[M5eE'j RC
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2492 Card=4247 BytesITPUB个人空间%`tks*mMrk
          =237832)

   1    0   SORT (UNIQUE) (Cost=2492 Card=4247 Bytes=237832)ITPUB个人空间L-bN2zU wP
   2    1     HASH JOIN (Cost=2450 Card=4247 Bytes=237832)
\$e7wMH(g HpG
|0   3    2       TABLE ACCESS (FULL) OF 'tb_test' (Cost=6 Card=3667 B
_m6|$fr g0          ytes=62339)

   4    2       TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_test_BET_L
7P o%t(sS-Ao&]0          OG' (Cost=10 Card=36 Bytes=324)

   5    4         NESTED LOOPS (Cost=2442 Card=4247 Bytes=165633)ITPUB个人空间0B'fI4@,wf,p!Wn
   6    5           HASH JOIN (Cost=1262 Card=118 Bytes=3540)ITPUB个人空间
e+i9?Cz!i9H1W s
   7    6             HASH JOIN (Cost=9 Card=1 Bytes=21)ITPUB个人空间?\YvX
   8    7               VIEW (Cost=2 Card=20 Bytes=80)ITPUB个人空间2|&e&AoF1~!v
   9    8                 CONNECT BY (WITH FILTERING)
6qCx3[#T5i*Z0  10    9                   NESTED LOOPS
,v1z8^O(qG _m5e6W0  11   10                     INDEX (UNIQUE SCAN) OF 'PK_TB_ADMIN_ROLE
x8A9o'ev@
P%H I0          ' (UNIQUE) (Cost=1 Card=1 Bytes=4)

  12   10                     TABLE ACCESS (BY USER ROWID) OF 'TB_ADMI
5A9F L+c:X1u(w]0          N_ROLE'

  13    9                   NESTED LOOPS
Uv?Qs a0  14   13                     BUFFER (SORT)ITPUB个人空间r4^c!j NP?9{,b
  15   14                       CONNECT BY PUMP
({:Q xze(k{0  16   13                     INDEX (RANGE SCAN) OF 'INX_ADMIN_ROLE_P'ITPUB个人空间+}Y'^H7x&A2[5_
           (NON-UNIQUE) (Cost=2 Card=20 Bytes=160)

  17    7              TABLE ACCESS (FULL) OF 'tb_test' (Cost=6CarITPUB个人空间u P-u7dR(\C|
          d=37 Bytes=629)

  18    6             PARTITION RANGE (ALL)
C;p(x M x.Xb
N&y0  19   18               TABLE ACCESS (FULL) OF 'TB_test_BET_LOG' (Cost
8X\)?d1p-K.t0          =1246Card=2316228 Bytes=20846052)

  20    5           INDEX (RANGE SCAN) OF 'IDX_test_DEAL_LOG1' (NON-UNITPUB个人空间C(nm&W
`(wdV
          IQUE) (Cost=2 Card=36)

Statistics
C/L,KT%T X/^R0X7K0----------------------------------------------------------ITPUB个人空间T5d$o xG3N}["g
          0  recursive callsITPUB个人空间4mh
c;g C
          0  db block gets
$U&w Y2vj3P@Y!t0      39295  consistent gets
&T3p5s:Sw9yU0      33639  physical readsITPUB个人空间:MRl)o*q
          0  redo sizeITPUB个人空间D%F%q3A&X
       4667  bytes sent via SQL*Net to clientITPUB个人空间 `3tt!\I S5[X-q
        628  bytes received via SQL*Net from client
#vK.O#l4VH/G0         14  SQL*Net roundtrips to/from client
olQ5[w0         16  sorts (memory)ITPUB个人空间Du7X!W[$T
          0  sorts (disk)
&K[/X
nad6l0        189  rows processed

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

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

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

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

看下面优化后的语句

SQL> Select distinct Id, Role_Id, LoginITPUB个人空间:Lgr+h0x*W!c
a
  2    From tb_test a,(Select Client_IdITPUB个人空间2bx2o7wu4LSo
  3            From Tb_test_Bet_Log y,
Dn-Uv,Qq By0  4             (Select Distinct a.test_Deal_Log_Id,created_dateITPUB个人空间"T8H3V$p#VJ z_"x8g
  5                    From Tb_test_Bet_Log a,   (Select IdITPUB个人空间6G&m4ys X
  6                            From tb_testITPUB个人空间1xs7^3L&`t5Z
  7                           Where Exists (Select IdITPUB个人空间$[3T Y1wDXk
  8                                    From (Select Id
3@ZGGb q0N?1Y;Nt`0  9                                            From Tb_Admin_RoleITPUB个人空间)T1C_@NU [-w
 10                                          Connect By Prior Id = Parent_IdITPUB个人空间,l4O%V H)`@XU'U
 11                                           Start With Id = 1) x
&Na9~dGv0 12                                   Where Role_Id = x.Id)ITPUB个人空间|uY3jfa KW
 13                             And Lower(Login) = 'rtest5') b
BDi
^T.Xu0 14                   Where a.Client_Id=b.id
'ED_^L @0 15                   and a.created_date between
!nPb1g2u.E-Q!XoL0 16                  to_date('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')ITPUB个人空间d4t)Ja1GT:j4E
 17                 and to_date('2007-01-03 23:59:59','yyyy-mm-dd hh24:mi:ss'))ITPUB个人空间sG^Y#d
b
!c0CxL4]OKRx0 18               Where y.test_Deal_Log_Id=b.test_Deal_Log_IdITPUB个人空间\gu.zOXV,i.^?
 19                 ) b
:y _0cK4Ut0 20   Where a.Id=b.client_id;

9|Yvt*su0Execution PlanITPUB个人空间:_*P,Xx4L#N,m'a
----------------------------------------------------------ITPUB个人空间@*_d"\z Q0r3R
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=36 Card=6 Bytes=384)ITPUB个人空间 |%IB5]!b K
   1    0   SORT (UNIQUE) (Cost=36 Card=6 Bytes=384)ITPUB个人空间Z$~/PdLzx}j/_
   2    1     NESTED LOOPS (Cost=34 Card=6 Bytes=384)ITPUB个人空间lT^X2p8A(c
   3    2       NESTED LOOPS (Cost=28 Card=6 Bytes=282)
u Zy.B/B]Y {5fo8J0   4    3         NESTED LOOPS (Cost=18 Card=1 Bytes=38)
Cuvp$Yh*R[ t0   5    4           HASH JOIN (Cost=9 Card=1 Bytes=21)
)W'e)o*J
M0   6    5             VIEW (Cost=2 Card=20 Bytes=80)ITPUB个人空间Jn'H1rr
   7    6               FILTERITPUB个人空间C6i;d0m{Al0M;~[@
   8    7                 CONNECT BY (WITH FILTERING)
7K2TB1P{a6|^0   9    8                   NESTED LOOPSITPUB个人空间b
y!M7dq6y3]8b1MZx
  10    9                     INDEX (UNIQUE SCAN) OF 'PK_TB_ADMIN_ROLEITPUB个人空间'|pU5ko0q
          ' (UNIQUE) (Cost=1 Card=1 Bytes=4)

  11    9                     TABLE ACCESS (BY USER ROWID) OF 'TB_ADMI
Lt^,DV*f#NoT0          N_ROLE'

  12    8                   NESTED LOOPSITPUB个人空间G C0i5F+P_)i/r
  13   12                     BUFFER (SORT)
H"I;zF]z%M0  14   13                       CONNECT BY PUMP
(T k;u)X [.b6]
\0  15   12                     INDEX (RANGE SCAN) OF 'INX_ADMIN_ROLE_P'ITPUB个人空间'WH#DxbGC1^B
           (NON-UNIQUE) (Cost=2 Card=20 Bytes=160)

  16    5             TABLE ACCESS (FULL) OF 'tb_test' (Cost=6 Card=
9z:D
z2C8y d|#b0          37 Bytes=629)

  17    4           TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TB_test_BEITPUB个人空间OU3mR*[d+l l
          T_LOG' (Cost=9 Card=2 Bytes=34)

  18   17            INDEX (RANGE SCAN) OF 'IDX_test_BET_LOG_CREATED_ITPUB个人空间'O2id"v4p5X Y3b9~*eF
          DATE' (NON-UNIQUE) (Cost=1 Card=82)

  19    3         TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_test_BET
~Sx]*rF,t0          _LOG' (Cost=10 Card=36 Bytes=324)

  20   19           INDEX (RANGE SCAN) OF 'IDX_test_DEAL_LOG1' (NON-UNITPUB个人空间[:sJP`B
E)x&P
          IQUE) (Cost=2 Card=36)

  21    2       TABLE ACCESS (BY INDEX ROWID) OF 'tb_test' (Cost=1 C
z T4m7L;Z {Rs)c0          ard=1 Bytes=17)

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

ITPUB个人空间AS2BnD'yf
Statistics
3A8v;Q[0[U:A^0----------------------------------------------------------
&{5p K `r{)e(F0          0  recursive callsITPUB个人空间.~I#M{f6Dq0v"WB1{\1|
          0  db block gets
2~I[[$Q,d0       1025  consistent getsITPUB个人空间.\J6[
J'Tio
          0  physical reads
$S5Z}b-T&b:D2x
Wo0          0  redo sizeITPUB个人空间]S^mH
        407  bytes sent via SQL*Net to clientITPUB个人空间:UE aZoS-T(w/?LT1J
        496  bytes received via SQL*Net from clientITPUB个人空间 Q],]p.u6y@5][
          2  SQL*Net roundtrips to/from clientITPUB个人空间E]WbZA_ S
         16  sorts (memory)
6N4c ^w'A0          0  sorts (disk)ITPUB个人空间8b%iN[:j.DC(G
          1  rows processed

SQL>
-O6^1Ob ZB#Jp0用上索引,逻辑读和物理读都降下来了

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