華章培訓網、[]華章培訓網多表連接的優化要點表連接的順序表連接的方式多表連接的問題最終被分解為多次雙表連接本節關注的和不關注的細節本節關注的內容：復雜表連接優化的思路和方法子查詢優化的要點外聯接優化CONNECT

BY的優化本節不關注的內容：執行計劃分析表連接方式及相關優化要點表連接的順序的優化建議表連接順序最好從業務的角度考慮多表連接可以一層一層分析，每次只考慮兩張表表連接順序的選擇原則是盡快減少連接結果集的大小表連接優化可能面臨的問題無法從業務人員得到所需的信息連接條件十分復雜連接的表很多無法獲得綁定變量的值多表連接的分析（1）分析WHERE條件分析WHERE條件了解每張表的過濾條件了解表之間的連接關系分析可能的連接順序確定最佳連接順序多表連接的分析（2）分析子查詢為了便于分析部分子查詢可以改寫為表連接注意等價原則常見子查詢模式EXISTS/NOT

EXISTSIN/NOT

INselect

d.dname

from

dept

d

where

exists(select

1

from

emp

e where

e.deptno

isnot

nulland

d.deptno=e.deptno

ande.ename

isnot

nulland

e.empno

>

1000)

and

d.deptnoisnot

null;select

d.dname

from

dept

dwhere

d.deptno

in

(select

e.deptno

from

emp

e);select

ename

from

empwhere

deptno

not

in

(select

deptno

from

dept)and

deptno

is

not

null;多表連接的分析（3）分析VIEW和INLINE

VIEWMERGE

VIEW不合理往往導致性能問題大部分INLINE

VIEW可以改寫為等價表連接部分VIEW和INLINEVIEW可以用物化視圖來優化多表連接的分析（4）分析表連接方式表連接方式對性能影響極大大多數多表連接出現突發性能問題和表連接方式有關主要的表連接方式NESTED

LOOPHASH

JOINSORT

MERGEJOIN案例（1）優化一個復雜SQLselect

acct_id,billing_cycle,bill_item_name,item_source_name,to_

char(sum(amount),'FM999999990.90')

amount,stateName,stateDate

from(

select

a.acct_id

,c.billing_cycle,d.bill_item_name,

item_source_name,a.amount,

stateName,to_char(a.state_date,'yyyy-mm-dd

hh24:mi:ss')

stateDate

from

(select

acct_item_type_id

,billing_cycle_id,acct_id,item_source_id,state,state_date,sum

(amount/100)

amount

from(

select

*

from

acct_item

where

acct_id

=165440341

and

serv_id

=169002286374

and

billing_cycle_id

in(10906)

)

group

by

acct_item_type_id,billing_cycle_id,acct_id,item_source_id,state

,state_date)

a,

(select

billing_cycle_id

,to_char(cycle_end_date

-1,'yyyy-mm')

billing_cycle

from

billing_cycle

where

state

in('10A','10R','10E','10D')

and

billing_cycle_idin(10906))c

,(select

x.acct_item_type_id

,y.bill_item_name,x.item_source_id

from

bill_item_acct_item

x

,bill_item

y

,

(selectdistinct

invoice_require_id

from

serv_acct

where

acct_id=165440341andserv_id

=169002286374

)

ee

,

bill_requement

ff,bill_format_bill_item

gg

where

x.bill_item_type_id

=

y.bill_item_type_idand

ee.invoice_require_id

=

ff.require_id

and

y.classify='55A'and

ff.bill_format_id

=

gg.bill_format_id

and

gg.bill_item_type_id

=

x.bill_item_type_id)d

,acct_item_source

e,

(select

domain,name

from

v_domain

where

table_name

='ACCT_ITEM'

andfield_name='STATE'

)

f

where

a.billing_cycle_id

=

c.billing_cycle_id

and

a.acct_item_type_id=

d.acct_item_type_id

and

a.item_source_id

=

d.item_source_id

and

a.item_source_id

=e.item_source_id

and

e.item_source_type='52A'

and

a.state=f.domain

)

group

by

acct_id

,billing_cycle,bill_item_name,item_source_name,stateName,stateDate

order

by

billing_cycle一個SQL的分析過程(1)格式化文本select acct_id,billing_cycle,bill_item_name,item_source_name,

to_char(sum(amount),'FM999999990.90')amount,stateName,stateDateFrom(

select a.acct_id

,c.billing_cycle,d.bill_item_name,

item_source_name,a.amount,stateName,to_char(a.state_date,'yyyy-mm-dd

hh24:mi:ss')

stateDatefrom(select

acct_item_type_id

,billing_cycle_id,acct_id,item_source_id,state,state_date,sum(amount/100)

amount

from (

select

*

from

acct_item

where

acct_id

=165440341

and

serv_id

=169002286374

andbilling_cycle_id

in

(10906) )

group

by

acct_item_type_id,billing_cycle_id,acct_id,item_source_id,state

,state_date )

a,(select

billing_cycle_id

,to_char(cycle_end_date

-1,'yyyy-mm')

billing_cycle from

billing_cyclewhere

state

in('10A','10R','10E','10D')

and billing_cycle_id

in(10906) )c

,(select

x.acct_item_type_id

,y.bill_item_name,x.item_source_id from

bill_item_acct_item

x

,bill_item

y

,(select

distinct

invoice_require_id

from

serv_acct

where

acct_id=165440

341

and

serv_id

=169002286374)

ee,

bill_requement

ff,bill_form

at_bill_item

ggwhere

x.bill_item_type_id

=

y.bill_item_type_id

and

ee.invoice_require_id

=

ff.require_id

and

y.classify='55A'

and

ff.bill_format_id

=

gg.bill_format_id

and gg.bill_item_type_id

=

x.bill_item_type_id)d

,acct_item_source

e,(select

domain,name

from

v_domain

where

table_name

='ACCT_ITEM'

and

field_name='STATE'

)

fwherea.billing_cycle_id

=

c.billing_cycle_id

anda.acct_item_type_id=

d.acct_item_type_id

anda.item_source_id

=

d.item_source_id

anda.item_source_id

=e.item_source_id

and

e.item_source_type='52A'

anda.state=f.domain

)group

by

acct_id

,billing_cycle,bill_item_name,item_source_name,stateName,stateDateorder

by

billing_cycle一個SQL的分析過程(2)截取子查詢select a.acct_id

,c.billing_cycle,d.bill_item_name,

item_source_name,a.amount,stateName,to_char(a.state_date,'yyyy-mm-dd

hh24:mi:ss')

stateDatefrom(select

acct_item_type_id

,billing_cycle_id,acct_id,item_source_id,state,state_date,sum(amount/100)

amount

from (

select

*

from

acct_item

where

acct_id

=165440341

and

serv_id

=169002286374

andbilling_cycle_id

in

(10906) )

group

by

acct_item_type_id,billing_cycle_id,acct_id,item_source_id,state

,state_date )

a,(select

billing_cycle_id

,to_char(cycle_end_date

-1,'yyyy-mm')

billing_cycle from

billing_cyclewhere

state

in('10A','10R','10E','10D')

and billing_cycle_id

in(10906) )c

,(select

x.acct_item_type_id

,y.bill_item_name,x.item_source_id from

bill_item_acct_item

x

,bill_item

y

,(select

distinct

invoice_require_id

from

serv_acct

where

acct_id=165440

341

and

serv_id

=169002286374)

ee

,

bill_requement

ff,bill_form

at_bill_item

ggwhere

x.bill_item_type_id

=

y.bill_item_type_id

and

ee.invoice_require_id

=

ff.require_id

and

y.classify='55A'

and

ff.bill_format_id

=

gg.bill_format_id

and gg.bill_item_type_id

=

x.bill_item_type_id)d

,acct_item_source

e,(select

domain,name

from

v_domain

where

table_name

='ACCT_ITEM'

and

field_name='STATE'

)

fwherea.billing_cycle_id

=

c.billing_cycle_id

anda.acct_item_type_id=

d.acct_item_type_id

anda.item_source_id

=

d.item_source_id

anda.item_source_id

=e.item_source_id

and

e.item_source_type='52A'

anda.state=f.domain一個SQL的分析過程(3)識別行源視圖1：(select

acct_item_type_id

,billing_cycle_id,acct_id,item_source_id,state,state_date,sum(amount/100)

amount

from (

select

*

from

acct_item

where

acct_id

=165440341

and

serv_id

=169002286374and billing_cycle_id

in

(10906) )

group

by

acct_item_type_id,billing_cycle_id,acct_id,item_source_id,state

,state_date)

a,視圖2：(select

billing_cycle_id

,to_char(cycle_end_date

-1,'yyyy-mm')

billing_cyclefrom

billing_cyclebilling_cycle_id

in(10906)where

state

in('10A','10R','10E','10D')

and)c視圖3：(select

x.acct_item_type_id

,y.bill_item_name,x.item_source_id

from

bill_item_acct_item

x

,bill_item

y

,(select

distinct

invoice_require_id

from

serv_acct

where

acct_id=165440

341

and

serv_id=169002286374)

ee

,bill_requement

ff,bill_form

at_bill_item

ggwhere

x.bill_item_type_id

=

y.bill_item_type_id

and

ee.invoice_require_id

=

ff.require_id

andgg.bill_item_type_id

=

x.bill_item_type_idy.classify='55A'

and

ff.bill_format_id

=

gg.bill_format_id

and)d表1：acct_item_sourcee,視圖4：(select

domain,name

from

v_domain

where

table_name

='ACCT_ITEM'

and

field_name='STATE'

)

f一個SQL的分析過程(4)識別連接條件a.billing_cycle_id

=

c.billing_cycle_id

anda.acct_item_type_id=

d.acct_item_type_id

anda.item_source_id

=

d.item_source_id

anda.item_source_id

=e.item_source_id

ande.item_source_type='52A'anda.state=f.domain以A核心，其他表均通過A進行連接A上無過濾條件，唯一的過濾條件是E上的e.item_source_type='52A‘連接順序應該是首選A+E連接，再考慮和其他表連接一個SQL的分析過程(5)分析A+E連接(selectacct_item_type_id

,billing_cycle_id,acct_id,item_source_id,state,state_date,sum(amount/100)

amount

from (

select

*

from

acct_item

where

acct_id

=165440341

andserv_id=169002286374and billing_cycle_id

in

(10906) )

group

by

acct_item_type_id,billing_cycle_id,acct_id,

item_source_id,state

,state_date)

aacct_item_source

eA+E連接的等價SQLselect

acct_item_type_id

,billing_cycle_id,acct_id,item_source_id,state,state_date,sum(amount/100)

amount

from(

select

*from

acct_item

X1,

acct_item_source

X2wherex1.acct_id

=165440341

andx1.serv_id

=169002286374

andx1.billing_cycle_id

in

(10906)

andx2.

item_source_type='52A‘

andx1.item_source_id

=x1.item_source_id)

group

by

acct_item_type_id,billing_cycle_id,acct_id,

item_source_id,state

,state_date一個SQL的分析過程(6)分析第一層連接select

acct_item_type_id,billing_cycle_id,acct_id,item_source_id,state,state_date,sum(amount/100)amount

from(

select

*fromacct_item

X1,

acct_item_source

X2where

x1.acct_id

=165440341

andx1.serv_id

=169002286374

andx1.billing_cycle_id

in

(10906)

andx2.

item_source_type='52A‘

andx1.item_source_id

=x1.item_source_id)

group

by

acct_item_type_id,billing_cycle_id,acct_id,

item_source_id,state

,state_date分析表連接的方式分析結果集的大小一個SQL的分析過程(7)創建用于分析的臨時表CREATE

TABLE

TMP_TBL1ASselect

acct_item_type_id,billing_cycle_id,acct_id,item_source_id,state,state_date,sum(amount/100)amount

from(

select

*fromacct_item

X1,

acct_item_source

X2where

x1.acct_id

=165440341

andx1.serv_id

=169002286374

andx1.billing_cycle_id

in

(10906)

andx2.

item_source_type='52A‘

andx1.item_source_id

=x1.item_source_id)

group

by

acct_item_type_id,billing_cycle_id,acct_id,

item_source_id,state

,state_date;EXEC

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>’…’,TABNAME=>’TMP_TBL1);一個SQL的分析過程(8)測試T+C的連接select

c.*(select

billing_cycle_id

,to_char(cycle_end_date

-1,'yyyy-mm')

billing_cyclefrom

billing_cyclewhere

state

in('10A','10R','10E','10D')

andbilling_cycle_id

in(10906))c

,tmp_tbl

twheret.billing_cycle_id

=

c.billing_cycle_id一個SQL的分析過程(9)測試T+D的連接select

d.*(select

x.acct_item_type_id

,y.bill_item_name,x.item_source_idfrom

bill_item_acct_item

x

,bill_item

y

,(select

distinct

invoice_require_id

from

serv_acct

where

acct_id=165440

341

andserv_id

=169002286374)

ee

,

bill_requement

ff,bill_form

at_bill_item

ggwherex.bill_item_type_id

=

y.bill_item_type_id

and

ee.invoice_require_id=

ff.require_idandy.classify='55A'

and

ff.bill_format_id

=

gg.bill_format_id

andgg.bill_item_type_id

=

x.bill_item_type_id)

d

,tmp_tbl1

twheret.acct_item_type_id=

d.acct_item_type_id

andt.item_source_id

=

d.item_source_id一個SQL的分析過程(10)測試T+F的連接select

d.*(select

domain,name

from

v_domainwhere

table_name

='ACCT_ITEM'

and

fi