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
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
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);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45962 | 11M| | 45650 (0)|
| 1 | SORT GROUP BY | | 45962 | 11M| 23M| 45650 (0)|
|* 2 | HASH JOIN | | 45962 | 11M| | 43908 (0)|
| 3 | INDEX FULL SCAN | T_FEE_TYPE_IDX_003 | 106 | 636 | | 1 (0)|
| 4 | NESTED LOOPS OUTER | | 45962 | 11M| | 43906 (0)|
|* 5 | HASH JOIN | | 45962 | 7271K| 6824K| 43905 (0)|
| 6 | NESTED LOOPS | | 45961 | 6283K| | 42312 (0)|
|* 7 | HASH JOIN SEMI | | 45961 | 5655K| 50M| 33120 (1)|
|* 8 | HASH JOIN OUTER |