15 - access("indexjoin_alias_012".ROWID="indexjoin_alias_011".ROWID)
19 - filter("T_COMPANY_ORGAN"."ORGAN_ID"='101')
21 - access("T_COMPANY_ORGAN"."ORGAN_ID"='101')
26 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL)
28 - access("TPC"."CHANGE_ID"="TPF"."CHANGE_ID")
31 - access("D"."COMPANY_ID"="E"."COMPANY_ID"(+))
55 rows selected
Statistics
----------------------------------------------------------
21 recursive calls
0 db block gets
125082 consistent gets
21149 physical reads
0 redo size
2448 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
11 rows processed
这个SQL要21秒才能跑完，逻辑读12W左右，问我能不能优化。优化这个SQL我只花了1分钟左右的时间，因为太简单了
你们看这个SQL是典型的JOIN，对付这种SQL肯定要让表走索引，但是从执行计划上看有个1千万行的表T_CONTRACT_MASTER走的是全表扫描，
T_POLICY_FEE 这个400W行的表也是走全表扫描，那么它不慢才怪呢，然后SQL的过滤条件有个in 子查询
(select
organ_id
from t_company_organ
start with organ_id = '101'
connect by prior organ_id = parent_id)
从执行计划上看，CBO对这儿子查询进行了unnest，因为通常情况下CBO认为子查询被unnest之后性能好于filter
于是我让盖尔查询 子查询返回多少行
select organ_id
from t_company_organ
start with organ_id = '101'
connect by prior organ_id = parent_id ---盖尔说它返回1行
对于子查询，如果它返回数据很少(这里返回1行)，那么可以让它走filter, 而且filter基本上是在SQL最后去阶段执行，这样t_policy_fee就可以走索引了
所以我给这个子查询加了个HINT，禁止子查询扩展
view plainprint
select tpc.policy_id,
tcm.policy_code,
tpf.organ_id,
to_char(tpf.insert_time, 'YYYY-MM-DD') As insert_time,
tpc.change_id,
d.policy_code,
e.company_name,
f.real_name,
tpf.fee_type,
sum(tpf.pay_balance) as pay_balance,
c.actual_type,
tpc.notice_code,
d.policy_type,
g.mode_name as pay_mode
from t_policy_change tpc,
t_contract_master tcm,
t_policy_fee tpf,
t_fee_type c,
t_contract_master d,
t_company_customer e,
t_customer f,
t_pay_mode g
where tpc.change_id = tpf.change_id
and tpf.policy_id = d.policy_id
and tcm.policy_id = tpc.policy_id
and tpf.receiv_status = '1' ---这里原来没引号，是开发那SB搞忘了写'',我让盖尔添加上了，不添加上就没法用索引
and tpf.fee_status = 1
and tpf.payment_id is null
and tpf.fee_type = c.type_id
and tpf.pay_mode = g.mode_id
and d.company_id = e.company_id(+)
and d.applicant_id = f.customer_id(+)
and tpf.organ_id in
(select /*+ no_unnest */ --此处的HINT后加的
organ_id
from t_company_organ
start with organ_id = '101'
connect by prior organ_id = parent_id)
group by tpc.policy_id,
tpc.change_id,
tpf.fee_type,
to_char(tpf.insert_time, 'YYYY-MM-DD'),
c.actual_type,
d.policy_code,
g.mode_name,
e.company_name,
f.real_name,
tpc.notice_code,
d.policy_type,
tpf.organ_id,
tcm.policy_code
order by change_id, fee_type
SQL> select * from table(dbms_xplan.display);