oracle数据库性能优化实务第24讲多表连接

华章培训网、[]华章培训网多表连接的优化要点表连接的顺序表连接的方式多表连接的问题最终被分解为多次双表连接本节关注的和不关注的细节本节关注的内容：复杂表连接优化的思路和方法子查询优化的要点外联接优化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