关于效率问题请教，第一个语句执行很慢,要40几秒，后面语句执行很快，4秒就搞定了。后面的语句是公司的DBA帮忙改的，但是看的不是很明白，请大家指导,第二个就是加了HIINT,修改了部分顺序
再有就是为什么第一个语句的执行计划我按F5看的不够详细?
select sum(1), a.order_sts
from cc_flow_order_monitor a, cc_flow_msg_access b, cc_flow_msg_usr c
where a.order_id = b.order_id
and a.order_id = c.order_id
and a.audit_dept_id in
(select to_char(org_id)
from organization
where org_idsuper_org_id
Start With super_org_id = '5090000'
Connect By Prior org_id = super_org_id
union
select to_char(org_id) from organization where org_id = '5090000')
and a.create_date >= to_date('20061003', 'YYYYMMDD')
and a.create_datesuper_org_id
Start With super_org_id = '5090000'
Connect By Prior org_id = super_org_id
union
select to_char(org_id) from organization where org_id = '5090000') D,
cc_flow_order_monitor a, cc_flow_msg_access b, cc_flow_msg_usr c
where a.order_id = b.order_id
and a.order_id = c.order_id
and a.audit_dept_id =D.ORG_ID
and a.create_date >= to_date('20061003', 'YYYYMMDD')
and a.create_datesuper_org_id
Start With super_org_id = '5090000'
Connect By Prior org_id = super_org_id
union
select to_char(org_id) from organization where org_id = '5090000')
and a.create_date >= to_date('20061003', 'YYYYMMDD')
and a.create_datesuper_org_id
Start With super_org_id = '5090000'
Connect By Prior org_id = super_org_id
union
select to_char(org_id) from organization where org_id = '5090000') D,
cc_flow_order_monitor a, cc_flow_msg_access b, cc_flow_msg_usr c
where a.order_id = b.order_id
and a.order_id = c.order_id
and a.audit_dept_id =D.ORG_ID
and a.create_date >= to_date('20061003', 'YYYYMMDD')
and a.create_date = to_date('20061003', 'YYYYMMDD')
and a.create_datesuper_org_id
Start With super_org_id = '5090000'
Connect By Prior org_id = super_org_id
union
select to_char(org_id) from organization where org_id = '5090000'
这个东西显然也不可能作为nest-loop的非驱动表得到好处，所以选择hash join了……
之所以显示REMOTE 而没有详细的执行计划，是因为organization时一个远程表/View，Oracle 让它返回一个数据集就可以了，不用管在远程机器上是什么执行计划。
|
