--------原始SQL
select e.* ,
aa.* ,
bb.* ,
ee.* ,
dd.*
from b_m_sys_branch e
left join
--购买金额
(select nvl(c.SEC_ORG_ID, '其他') as SEC_ORG_ID,
max(nvl(c.SEC_ORG_NAME, '其他')) as SEC_ORG_NAME,
sum(a.finance_amt) as 购买金额
from (SELECT DISTINCT a.RECOM_OPER_NO,
a.cust_no,
a.cust_name,
a.product_no,
a.contract_no,
a.finance_amt,
a.sign_date
FROM DWF.F_EVT_EXTR_FINANCE_BOOK a
where a.valid_flag = '1' --有效标志
AND a.deduct_flag = '1' -- 扣款标志
AND a.acct_flag = '0') a
left join dwf.f_extc_finance_para b
on a.product_no = b.product_no
left join dwm.v_m_pty_emp_info c
on a.recom_oper_no = c.PTY_ID
and c.START_DT <= to_date('2014-03-21', 'YYYY-MM-DD')
and c.END_DT > to_date('2014-03-21', 'YYYY-MM-DD')
where b.freeze_start_date >= to_date('2014-01-01', 'YYYY-MM-DD')
and b.freeze_start_date <= to_date('2014-03-21', 'YYYY-MM-DD')
group by c.SEC_ORG_ID) aa
on e.org_id = aa.SEC_ORG_ID
left join (
--存量数
select nvl(c.SEC_ORG_ID, '其他') as SEC_ORG_ID,
max(nvl(c.SEC_ORG_NAME, '其他')) as SEC_ORG_NAME,
count(distinct a.cust_no) as 存量客户数,
sum(a.finance_amt) as 存量金额
from (SELECT DISTINCT a.RECOM_OPER_NO,
a.cust_no,
a.cust_name,
a.product_no,
a.contract_no,
a.finance_amt,
a.sign_date
FROM DWF.F_EVT_EXTR_FINANCE_BOOK a
where a.valid_flag = '1' --有效标志
AND a.deduct_flag = '1' -- 扣款标志
AND a.acct_flag = '0') a
left join dwf.f_extc_finance_para b
on a.product_no = b.product_no
left join dwm.v_m_pty_emp_info c
on a.RECOM_OPER_NO = c.PTY_ID
and c.START_DT <= to_date('2014-03-21', 'YYYY-MM-DD')
and c.END_DT > to_date('2014-03-21', 'YYYY-MM-DD')
where b.freeze_start_date < to_date('2014-03-21', 'YYYY-MM-DD')
and b.freeze_end_date > to_date('2014-03-21', 'YYYY-MM-DD')
group by c.SEC_ORG_ID) bb
on e.org_id = bb.SEC_ORG_ID
left join (
--存量数日均
select nvl(c.SEC_ORG_ID, '其他') as SEC_ORG_ID,
max(nvl(c.SEC_ORG_NAME, '其他')) as SEC_ORG_NAME,
sum(case
when b.freeze_start_date >=
to_date('2014-01-01',
'YYYY-MM-DD') then
(to_date('2014-03-21',
'YYYY-MM-DD') -
b.freeze_start_date ) *
a.finance_amt / 365
when b.freeze_start_date <
to_date('2014-01-01',
'YYYY-MM-DD') then
(to_date('2014-03-21',
'YYYY-MM-DD') - to_date('2014-01-01',
'YYYY-MM-DD')
) *
a.finance_amt / 365
end) 存量日均
from (SELECT DISTINCT a.RECOM_OPER_NO,
a.cust_no,
a.cust_name,
a.product_no,
a.contract_no,
a.finance_amt,
a.sign_date
FROM DWF.F_EVT_EXTR_FINANCE_BOOK a
where a.valid_flag = '1' --有效标志
AND a.deduct_flag = '1' -- 扣款标志
AND a.acct_flag = '0') a
left join dwf.f_extc_finance_para b
on a.product_no = b.product_no
left join dwm.v_m_pty_emp_info c
on a.RECOM_OPER_NO = c.PTY_ID
and c.START_DT <= to_date('2014-03-21', 'YYYY-MM-DD')
and c.END_DT > to_date('2014-03-21', 'YYYY-MM-DD')
where b.freeze_start_date < to_date('2014-03-21', 'YYYY-MM-DD')
and b.freeze_end_date > to_date('2014-03-21', 'YYYY-MM-DD')
group by c.SEC_ORG_ID) ee
on e.org_id = ee.SEC_ORG_ID
left join (
--到期金额
select nvl(c.SEC_ORG_ID, '其他') as SEC_ORG_ID,
max(nvl(c.SEC_ORG_NAME, '其他')) as SEC_ORG_NAME,
sum(a.finance_amt) as 到期金额
from (SELECT DISTINCT a.RECOM_OPER_NO,
a.cust_no,
a.cust_name,
a.product_no,
a.contract_no,
a.finance_amt,
a.sign_date
FROM DWF.F_EVT_EXTR_FINANCE_BOOK a
where a.valid_flag = '1' --有效标志
AND a.deduct_flag = '1' -- 扣款标志
AND a.acct_flag <> '0'
) a
left join dwf.f_extc_finance_para b
on a.product_no = b.product_no
left join dwm.v_m_pty_emp_info c
on a.RECOM_OPER_NO = c.PTY_ID
and c.START_DT <= to_date('2014-03-21', 'YYYY-MM-DD')
and c.END_DT > to_date('2014-03-21', 'YYYY-MM-DD')
where b.freeze_end_date >= to_date('2014-01-01', 'YYYY-MM-DD')
and b.freeze_end_date <= to_date('2014-03-21', 'YYYY-MM-DD')
group by c.SEC_ORG_ID) dd
on e.org_id = dd.SEC_ORG_ID ;
Plan hash value: 3940762133
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74M| 24G| | 332K (2)| 01:06:30 |
|* 1 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 18 | | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 18 | | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 18 | | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 18 | | 3 (0)| 00:00:01 |
|* 9 | HASH JOIN RIGHT OUTER | | 74M| 24G| | 332K (2)| 01:06:30 |
| 10 | VIEW | | 3992 | 261K| | 123K (1)| 00:24:45 |
| 11 | HASH GROUP BY | | 3992 | 378K| | 123K (1)| 00:24:45 |
| 12 | NESTED LOOPS OUTER | | 3992 | 378K| | 123K (1)| 00:24:45 |
|* 13 | HASH JOIN | | 592 | 24272 | | 6032 (1)| 00:01:13 |
| 14 | JOIN FILTER CREATE | :BF0000 | 3 | 45 | | 6 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | F_EXTC_FINANCE_PARA | 3 | 45 | | 6 (0)| 00:00:01 |
| 16 | VIEW | | 102K| 2599K| | 6025 (1)| 00:01:13 |
| 17 | HASH UNIQUE | | 102K| 6999K| 9648K| 6025 (1)| 00:01:13 |
| 18 | JOIN FILTER USE | :BF0000 | 102K| 6999K| | 4334 (1)| 00:00:53 |
|* 19 | TABLE ACCESS FULL | F_EVT_EXTR_FINANCE_BOOK | 102K| 6999K| | 4334 (1)| 00:00:53 |
| 20 | VIEW PUSHED PREDICATE | V_M_PTY_EMP_INFO | 7 | 392 | | 199 (2)| 00:00:03 |
| 21 | NESTED LOOPS OUTER | | 24277 | 2157K| | 199 (2)| 00:00:03 |
|* 22 | TABLE ACCESS BY INDEX ROWID | F_PTY_EMP_INFO | 7 | 182 | | 8 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | EMP_IDX_002 | 7 | | | 1 (0)| 00:00:01 |
|* 24 | VIEW PUSHED PREDICATE | | 1 | 65 | | 27 (0)| 00:00:01 |
|* 25 | HASH JOIN OUTER | | 1 | 76 | | 27 (0)| 00:00:01 |
| 26 | VIEW | | 1 | 62 | | 24 (0)| 00:00:01 |
|* 27 | HASH JOIN OUTER | | 1 | 99 | | 24 (0)| 00:00:01 |
|* 28 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
| 29 | VIEW | | 1 | 85 | | 21 (0)| 00:00:01 |
|* 30 | FILTER | | | | | | |
|* 31 | HASH JOIN OUTER | | 1 | 79 | | 21 (0)| 00:00:01 |
|* 32 | HASH JOIN OUTER | | 1 | 65 | | 18 (0)| 00:00:01 |
|* 33 | HASH JOIN OUTER | | 1 | 58 | | 15 (0)| 00:00:01 |
|* 34 | TABLE ACCESS BY INDEX ROWID | F_PTY_EMP_INFO | 1 | 24 | | 8 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | EMP_IDX_002 | 7 | | | 1 (0)| 00:00:01 |
| 36 | VIEW | M_CBS_TO_DW_ORG | 50 | 1700 | | 7 (0)| 00:00:01 |
| 37 | UNION-ALL | | | | | | |
|* 38 | TABLE ACCESS FULL | F_PTY_ORG | 35 | 770 | | 4 (0)| 00:00:01 |
|* 39 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 15 | 105 | | 3 (0)| 00:00:01 |
| 40 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 150 | 1050 | | 3 (0)| 00:00:01 |
|* 41 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 12 | 168 | | 3 (0)| 00:00:01 |
|* 42 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 112 | 1568 | | 3 (0)| 00:00:01 |
|* 43 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 23 | 322 | | 3 (0)| 00:00:01 |
|* 44 | HASH JOIN RIGHT OUTER | | 1523K| 408M| | 208K (1)| 00:41:39 |
| 45 | VIEW | | 2316 | 151K| | 73380 (1)| 00:14:41 |
| 46 | HASH GROUP BY | | 2316 | 237K| | 73380 (1)| 00:14:41 |
| 47 | NESTED LOOPS OUTER | | 2316 | 237K| | 73379 (1)| 00:14:41 |
|* 48 | HASH JOIN | | 343 | 16807 | | 5189 (1)| 00:01:03 |
| 49 | JOIN FILTER CREATE | :BF0001 | 4 | 92 | | 6 (0)| 00:00:01 |
|* 50 | TABLE ACCESS FULL | F_EXTC_FINANCE_PARA | 4 | 92 | | 6 (0)| 00:00:01 |
| 51 | VIEW | | 51195 | 1299K| | 5182 (1)| 00:01:03 |
| 52 | HASH UNIQUE | | 51195 | 3499K| 4832K| 5182 (1)| 00:01:03 |
| 53 | JOIN FILTER USE | :BF0001 | 51195 | 3499K| | 4334 (1)| 00:00:53 |
|* 54 | TABLE ACCESS FULL | F_EVT_EXTR_FINANCE_BOOK | 51195 | 3499K| | 4334 (1)| 00:00:53 |
| 55 | VIEW PUSHED PREDICATE | V_M_PTY_EMP_INFO | 7 | 392 | | 199 (2)| 00:00:03 |
| 56 | NESTED LOOPS OUTER | | 24277 | 2157K| | 199 (2)| 00:00:03 |
|* 57 | TABLE ACCESS BY INDEX ROWID | F_PTY_EMP_INFO | 7 | 182 | | 8 (0)| 00:00:01 |
|* 58 | INDEX RANGE SCAN | EMP_IDX_002 | 7 | | | 1 (0)| 00:00:01 |
|* 59 | VIEW PUSHED PREDICATE | | 1 | 65 | | 27 (0)| 00:00:01 |
|* 60 | HASH JOIN OUTER | | 1 | 76 | | 27 (0)| 00:00:01 |
| 61 | VIEW | | 1 | 62 | | 24 (0)| 00:00:01 |
|* 62 | HASH JOIN OUTER | | 1 | 99 | | 24 (0)| 00:00:01 |
|* 63 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
| 64 | VIEW | | 1 | 85 | | 21 (0)| 00:00:01 |
|* 65 | FILTER | | | | | | |
|* 66 | HASH JOIN OUTER | | 1 | 79 | | 21 (0)| 00:00:01 |
|* 67 | HASH JOIN OUTER | | 1 | 65 | | 18 (0)| 00:00:01 |
|* 68 | HASH JOIN OUTER | | 1 | 58 | | 15 (0)| 00:00:01 |
|* 69 | TABLE ACCESS BY INDEX ROWID | F_PTY_EMP_INFO | 1 | 24 | | 8 (0)| 00:00:01 |
|* 70 | INDEX RANGE SCAN | EMP_IDX_002 | 7 | | | 1 (0)| 00:00:01 |
| 71 | VIEW | M_CBS_TO_DW_ORG | 50 | 1700 | | 7 (0)| 00:00:01 |
| 72 | UNION-ALL | | | | | | |
|* 73 | TABLE ACCESS FULL | F_PTY_ORG | 35 | 770 | | 4 (0)| 00:00:01 |
|* 74 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 15 | 105 | | 3 (0)| 00:00:01 |
| 75 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 150 | 1050 | | 3 (0)| 00:00:01 |
|* 76 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 12 | 168 | | 3 (0)| 00:00:01 |
|* 77 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 112 | 1568 | | 3 (0)| 00:00:01 |
|* 78 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 23 | 322 | | 3 (0)| 00:00:01 |
|* 79 | HASH JOIN RIGHT OUTER | | 53946 | 11M| | 134K (1)| 00:26:59 |
| 80 | VIEW | | 1910 | 124K| | 61452 (1)| 00:12:18 |
| 81 | HASH GROUP BY | | 1910 | 180K| | 61452 (1)| 00:12:18 |
| 82 | NESTED LOOPS OUTER | | 1910 | 180K| | 61451 (1)| 00:12:18 |
|* 83 | HASH JOIN | | 283 | 11603 | | 5189 (1)| 00:01:03 |
| 84 | JOIN FILTER CREATE | :BF0002 | 3 | 45 | | 6 (0)| 00:00:01 |
|* 85 | TABLE ACCESS FULL | F_EXTC_FINANCE_PARA | 3 | 45 | | 6 (0)| 00:00:01 |
| 86 | VIEW | | 51195 | 1299K| | 5182 (1)| 00:01:03 |
| 87 | HASH UNIQUE | | 51195 | 3499K| 4832K| 5182 (1)| 00:01:03 |
| 88 | JOIN FILTER USE | :BF0002 | 51195 | 3499K| | 4334 (1)| 00:00:53 |
|* 89 | TABLE ACCESS FULL | F_EVT_EXTR_FINANCE_BOOK | 51195 | 3499K| | 4334 (1)| 00:00:53 |
| 90 | VIEW PUSHED PREDICATE | V_M_PTY_EMP_INFO | 7 | 392 | | 199 (2)| 00:00:03 |
| 91 | NESTED LOOPS OUTER | | 24277 | 2157K| | 199 (2)| 00:00:03 |
|* 92 | TABLE ACCESS BY INDEX ROWID | F_PTY_EMP_INFO | 7 | 182 | | 8 (0)| 00:00:01 |
|* 93 | INDEX RANGE SCAN | EMP_IDX_002 | 7 | | | 1 (0)| 00:00:01 |
|* 94 | VIEW PUSHED PREDICATE | | 1 | 65 | | 27 (0)| 00:00:01 |
|* 95 | HASH JOIN OUTER | | 1 | 76 | | 27 (0)| 00:00:01 |
| 96 | VIEW | | 1 | 62 | | 24 (0)| 00:00:01 |
|* 97 | HASH JOIN OUTER | | 1 | 99 | | 24 (0)| 00:00:01 |
|* 98 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
| 99 | VIEW | | 1 | 85 | | 21 (0)| 00:00:01 |
|*100 | FILTER | | | | | | |
|*101 | HASH JOIN OUTER | | 1 | 79 | | 21 (0)| 00:00:01 |
|*102 | HASH JOIN OUTER | | 1 | 65 | | 18 (0)| 00:00:01 |
|*103 | HASH JOIN OUTER | | 1 | 58 | | 15 (0)| 00:00:01 |
|*104 | TABLE ACCESS BY INDEX ROWID | F_PTY_EMP_INFO | 1 | 24 | | 8 (0)| 00:00:01 |
|*105 | INDEX RANGE SCAN | EMP_IDX_002 | 7 | | | 1 (0)| 00:00:01 |
| 106 | VIEW | M_CBS_TO_DW_ORG | 50 | 1700 | | 7 (0)| 00:00:01 |
| 107 | UNION-ALL | | | | | | |
|*108 | TABLE ACCESS FULL | F_PTY_ORG | 35 | 770 | | 4 (0)| 00:00:01 |
|*109 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 15 | 105 | | 3 (0)| 00:00:01 |
| 110 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 150 | 1050 | | 3 (0)| 00:00:01 |
|*111 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 12 | 168 | | 3 (0)| 00:00:01 |
|*112 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 112 | 1568 | | 3 (0)| 00:00:01 |
|*113 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 23 | 322 | | 3 (0)| 00:00:01 |
|*114 | HASH JOIN OUTER | | 2316 | 332K| | 73383 (1)| 00:14:41 |
| 115 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 82 | 5494 | | 3 (0)| 00:00:01 |
| 116 | VIEW | | 2316 | 180K| | 73380 (1)| 00:14:41 |
| 117 | HASH GROUP BY | | 2316 | 178K| | 73380 (1)| 00:14:41 |
| 118 | VIEW | VW_DAG_0 | 2316 | 178K| | 73380 (1)| 00:14:41 |
| 119 | HASH GROUP BY | | 2316 | 282K| | 73380 (1)| 00:14:41 |
| 120 | NESTED LOOPS OUTER | | 2316 | 282K| | 73379 (1)| 00:14:41 |
|*121 | HASH JOIN | | 343 | 23667 | | 5189 (1)| 00:01:03 |
| 122 | JOIN FILTER CREATE | :BF0003 | 4 | 92 | | 6 (0)| 00:00:01 |
|*123 | TABLE ACCESS FULL | F_EXTC_FINANCE_PARA | 4 | 92 | | 6 (0)| 00:00:01 |
| 124 | VIEW | | 51195 | 2299K| | 5182 (1)| 00:01:03 |
| 125 | HASH UNIQUE | | 51195 | 3499K| 4832K| 5182 (1)| 00:01:03 |
| 126 | JOIN FILTER USE | :BF0003 | 51195 | 3499K| | 4334 (1)| 00:00:53 |
|*127 | TABLE ACCESS FULL | F_EVT_EXTR_FINANCE_BOOK | 51195 | 3499K| | 4334 (1)| 00:00:53 |
| 128 | VIEW PUSHED PREDICATE | V_M_PTY_EMP_INFO | 7 | 392 | | 199 (2)| 00:00:03 |
| 129 | NESTED LOOPS OUTER | | 24277 | 2157K| | 199 (2)| 00:00:03 |
|*130 | TABLE ACCESS BY INDEX ROWID | F_PTY_EMP_INFO | 7 | 182 | | 8 (0)| 00:00:01 |
|*131 | INDEX RANGE SCAN | EMP_IDX_002 | 7 | | | 1 (0)| 00:00:01 |
|*132 | VIEW PUSHED PREDICATE | | 1 | 65 | | 27 (0)| 00:00:01 |
|*133 | HASH JOIN OUTER | | 1 | 76 | | 27 (0)| 00:00:01 |
| 134 | VIEW | | 1 | 62 | | 24 (0)| 00:00:01 |
|*135 | HASH JOIN OUTER | | 1 | 99 | | 24 (0)| 00:00:01 |
|*136 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
| 137 | VIEW | | 1 | 85 | | 21 (0)| 00:00:01 |
|*138 | FILTER | | | | | | |
|*139 | HASH JOIN OUTER | | 1 | 79 | | 21 (0)| 00:00:01 |
|*140 | HASH JOIN OUTER | | 1 | 65 | | 18 (0)| 00:00:01 |
|*141 | HASH JOIN OUTER | | 1 | 58 | | 15 (0)| 00:00:01 |
|*142 | TABLE ACCESS BY INDEX ROWID| F_PTY_EMP_INFO | 1 | 24 | | 8 (0)| 00:00:01 |
|*143 | INDEX RANGE SCAN | EMP_IDX_002 | 7 | | | 1 (0)| 00:00:01 |
| 144 | VIEW | M_CBS_TO_DW_ORG | 50 | 1700 | | 7 (0)| 00:00:01 |
| 145 | UNION-ALL | | | | | | |
|*146 | TABLE ACCESS FULL | F_PTY_ORG | 35 | 770 | | 4 (0)| 00:00:01 |
|*147 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 15 | 105 | | 3 (0)| 00:00:01 |
| 148 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 150 | 1050 | | 3 (0)| 00:00:01 |
|*149 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 12 | 168 | | 3 (0)| 00:00:01 |
|*150 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 112 | 1568 | | 3 (0)| 00:00:01 |
|*151 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 23 | 322 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:B1)
2 - filter("ID"=:B1)
3 - filter("ID"=:B1)
4 - filter("ID"=:B1)
5 - filter("ID"=:B1)
6 - filter("ID"=:B1)
7 - filter("ID"=:B1)
8 - filter("ID"=:B1)
9 - access("E"."ORG_ID"="DD"."SEC_ORG_ID"(+))
13 - access("A"."PRODUCT_NO"="B"."PRODUCT_NO")
15 - filter("B"."FREEZE_END_DATE"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."FREEZE_END_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
19 - filter("A"."VALID_FLAG"='1' AND "A"."DEDUCT_FLAG"='1' AND "A"."ACCT_FLAG"<>'0' AND
SYS_OP_BLOOM_FILTER(:BF0000,"A"."PRODUCT_NO"))
22 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."END_DT">TO_DATE('
2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
23 - access("T"."PTY_ID"="A"."RECOM_OPER_NO")
24 - filter("T"."SOURCE_CODE"="B"."SOURCE_CODE"(+))
25 - access("FST"."SEC_ORG_ID"="ORG_ID"(+))
27 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID"))
28 - filter("ID"=:B1)
30 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
31 - access("C"."ORG_ID"="ORG_ID"(+))
32 - access("B"."ORG_ID"="C"."ORG_ID"(+))
33 - access("A"."ORG_NO"="B"."PTY_ID"(+))
34 - filter("A"."START_DT"="T"."START_DT" AND "A"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
35 - access("A"."PTY_ID"="T"."PTY_ID")
38 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
39 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%' AND
"ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL)
41 - filter("ORG_LEVEL"(+)=4)
42 - filter("ORG_LEVEL"(+)=3)
43 - filter("ORG_LEVEL"(+)=2)
44 - access("E"."ORG_ID"="EE"."SEC_ORG_ID"(+))
48 - access("A"."PRODUCT_NO"="B"."PRODUCT_NO")
50 - filter("B"."FREEZE_START_DATE"<TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."FREEZE_END_DATE">TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
54 - filter("A"."ACCT_FLAG"='0' AND "A"."VALID_FLAG"='1' AND "A"."DEDUCT_FLAG"='1' AND
SYS_OP_BLOOM_FILTER(:BF0001,"A"."PRODUCT_NO"))
57 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."END_DT">TO_DATE('
2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
58 - access("T"."PTY_ID"="A"."RECOM_OPER_NO")
59 - filter("T"."SOURCE_CODE"="B"."SOURCE_CODE"(+))
60 - access("FST"."SEC_ORG_ID"="ORG_ID"(+))
62 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID"))
63 - filter("ID"=:B1)
65 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
66 - access("C"."ORG_ID"="ORG_ID"(+))
67 - access("B"."ORG_ID"="C"."ORG_ID"(+))
68 - access("A"."ORG_NO"="B"."PTY_ID"(+))
69 - filter("A"."START_DT"="T"."START_DT" AND "A"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
70 - access("A"."PTY_ID"="T"."PTY_ID")
73 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
74 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%' AND
"ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL)
76 - filter("ORG_LEVEL"(+)=4)
77 - filter("ORG_LEVEL"(+)=3)
78 - filter("ORG_LEVEL"(+)=2)
79 - access("E"."ORG_ID"="AA"."SEC_ORG_ID"(+))
83 - access("A"."PRODUCT_NO"="B"."PRODUCT_NO")
85 - filter("B"."FREEZE_START_DATE"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."FREEZE_START_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
89 - filter("A"."ACCT_FLAG"='0' AND "A"."VALID_FLAG"='1' AND "A"."DEDUCT_FLAG"='1' AND
SYS_OP_BLOOM_FILTER(:BF0002,"A"."PRODUCT_NO"))
92 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."END_DT">TO_DATE('
2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
93 - access("T"."PTY_ID"="A"."RECOM_OPER_NO")
94 - filter("T"."SOURCE_CODE"="B"."SOURCE_CODE"(+))
95 - access("FST"."SEC_ORG_ID"="ORG_ID"(+))
97 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID"))
98 - filter("ID"=:B1)
100 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
101 - access("C"."ORG_ID"="ORG_ID"(+))
102 - access("B"."ORG_ID"="C"."ORG_ID"(+))
103 - access("A"."ORG_NO"="B"."PTY_ID"(+))
104 - filter("A"."START_DT"="T"."START_DT" AND "A"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
105 - access("A"."PTY_ID"="T"."PTY_ID")
108 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
109 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%' AND
"ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL)
111 - filter("ORG_LEVEL"(+)=4)
112 - filter("ORG_LEVEL"(+)=3)
113 - filter("ORG_LEVEL"(+)=2)
114 - access("E"."ORG_ID"="BB"."SEC_ORG_ID"(+))
121 - access("A"."PRODUCT_NO"="B"."PRODUCT_NO")
123 - filter("B"."FREEZE_START_DATE"<TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."FREEZE_END_DATE">TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
127 - filter("A"."ACCT_FLAG"='0' AND "A"."VALID_FLAG"='1' AND "A"."DEDUCT_FLAG"='1' AND
SYS_OP_BLOOM_FILTER(:BF0003,"A"."PRODUCT_NO"))
130 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."END_DT">TO_DATE('
2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
131 - access("T"."PTY_ID"="A"."RECOM_OPER_NO")
132 - filter("T"."SOURCE_CODE"="B"."SOURCE_CODE"(+))
133 - access("FST"."SEC_ORG_ID"="ORG_ID"(+))
135 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID"))
136 - filter("ID"=:B1)
138 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
139 - access("C"."ORG_ID"="ORG_ID"(+))
140 - access("B"."ORG_ID"="C"."ORG_ID"(+))
141 - access("A"."ORG_NO"="B"."PTY_ID"(+))
142 - filter("A"."START_DT"="T"."START_DT" AND "A"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
143 - access("A"."PTY_ID"="T"."PTY_ID")
146 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
147 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%' AND
"ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL)
149 - filter("ORG_LEVEL"(+)=4)
150 - filter("ORG_LEVEL"(+)=3)
151 - filter("ORG_LEVEL"(+)=2)
其中dwm.v_m_pty_emp_info c 视图数据26980行
----------------------------------------------
alter session set "_push_join_predicate"=FALSE
select e.* ,
aa.* ,
bb.* ,
ee.* ,
dd.*
from b_m_sys_branch e
left join
--购买金额
(select nvl(c.SEC_ORG_ID, '其他') as SEC_ORG_ID,
max(nvl(c.SEC_ORG_NAME, '其他')) as SEC_ORG_NAME,
sum(a.finance_amt) as 购买金额
from (SELECT DISTINCT a.RECOM_OPER_NO,
a.cust_no,
a.cust_name,
a.product_no,
a.contract_no,
a.finance_amt,
a.sign_date
FROM DWF.F_EVT_EXTR_FINANCE_BOOK a
where a.valid_flag = '1' --有效标志
AND a.deduct_flag = '1' -- 扣款标志
AND a.acct_flag = '0') a
left join dwf.f_extc_finance_para b
on a.product_no = b.product_no
left join dwm.v_m_pty_emp_info c
on a.recom_oper_no = c.PTY_ID
and c.START_DT <= to_date('2014-03-21', 'YYYY-MM-DD')
and c.END_DT > to_date('2014-03-21', 'YYYY-MM-DD')
where b.freeze_start_date >= to_date('2014-01-01', 'YYYY-MM-DD')
and b.freeze_start_date <= to_date('2014-03-21', 'YYYY-MM-DD')
group by c.SEC_ORG_ID) aa
on e.org_id = aa.SEC_ORG_ID
left join (
--存量数
select nvl(c.SEC_ORG_ID, '其他') as SEC_ORG_ID,
max(nvl(c.SEC_ORG_NAME, '其他')) as SEC_ORG_NAME,
count(distinct a.cust_no) as 存量客户数,
sum(a.finance_amt) as 存量金额
from (SELECT DISTINCT a.RECOM_OPER_NO,
a.cust_no,
a.cust_name,
a.product_no,
a.contract_no,
a.finance_amt,
a.sign_date
FROM DWF.F_EVT_EXTR_FINANCE_BOOK a
where a.valid_flag = '1' --有效标志
AND a.deduct_flag = '1' -- 扣款标志
AND a.acct_flag = '0') a
left join dwf.f_extc_finance_para b
on a.product_no = b.product_no
left join dwm.v_m_pty_emp_info c
on a.RECOM_OPER_NO = c.PTY_ID
and c.START_DT <= to_date('2014-03-21', 'YYYY-MM-DD')
and c.END_DT > to_date('2014-03-21', 'YYYY-MM-DD')
where b.freeze_start_date < to_date('2014-03-21', 'YYYY-MM-DD')
and b.freeze_end_date > to_date('2014-03-21', 'YYYY-MM-DD')
group by c.SEC_ORG_ID) bb
on e.org_id = bb.SEC_ORG_ID
left join (
--存量数日均
select nvl(c.SEC_ORG_ID, '其他') as SEC_ORG_ID,
max(nvl(c.SEC_ORG_NAME, '其他')) as SEC_ORG_NAME,
sum(case
when b.freeze_start_date >=
to_date('2014-01-01',
'YYYY-MM-DD') then
(to_date('2014-03-21',
'YYYY-MM-DD') -
b.freeze_start_date ) *
a.finance_amt / 365
when b.freeze_start_date <
to_date('2014-01-01',
'YYYY-MM-DD') then
(to_date('2014-03-21',
'YYYY-MM-DD') - to_date('2014-01-01',
'YYYY-MM-DD')
) *
a.finance_amt / 365
end) 存量日均
from (SELECT DISTINCT a.RECOM_OPER_NO,
a.cust_no,
a.cust_name,
a.product_no,
a.contract_no,
a.finance_amt,
a.sign_date
FROM DWF.F_EVT_EXTR_FINANCE_BOOK a
where a.valid_flag = '1' --有效标志
AND a.deduct_flag = '1' -- 扣款标志
AND a.acct_flag = '0') a
left join dwf.f_extc_finance_para b
on a.product_no = b.product_no
left join dwm.v_m_pty_emp_info c
on a.RECOM_OPER_NO = c.PTY_ID
and c.START_DT <= to_date('2014-03-21', 'YYYY-MM-DD')
and c.END_DT > to_date('2014-03-21', 'YYYY-MM-DD')
where b.freeze_start_date < to_date('2014-03-21', 'YYYY-MM-DD')
and b.freeze_end_date > to_date('2014-03-21', 'YYYY-MM-DD')
group by c.SEC_ORG_ID) ee
on e.org_id = ee.SEC_ORG_ID
left join (
--到期金额
select nvl(c.SEC_ORG_ID, '其他') as SEC_ORG_ID,
max(nvl(c.SEC_ORG_NAME, '其他')) as SEC_ORG_NAME,
sum(a.finance_amt) as 到期金额
from (SELECT DISTINCT a.RECOM_OPER_NO,
a.cust_no,
a.cust_name,
a.product_no,
a.contract_no,
a.finance_amt,
a.sign_date
FROM DWF.F_EVT_EXTR_FINANCE_BOOK a
where a.valid_flag = '1' --有效标志
AND a.deduct_flag = '1' -- 扣款标志
AND a.acct_flag <> '0'
) a
left join dwf.f_extc_finance_para b
on a.product_no = b.product_no
left join dwm.v_m_pty_emp_info c
on a.RECOM_OPER_NO = c.PTY_ID
and c.START_DT <= to_date('2014-03-21', 'YYYY-MM-DD')
and c.END_DT > to_date('2014-03-21', 'YYYY-MM-DD')
where b.freeze_end_date >= to_date('2014-01-01', 'YYYY-MM-DD')
and b.freeze_end_date <= to_date('2014-03-21', 'YYYY-MM-DD')
group by c.SEC_ORG_ID) dd
on e.org_id = dd.SEC_ORG_ID ;
Plan hash value: 3624608596
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74M| 24G| | 23049 (4)| 00:04:37 |
|* 1 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 18 | | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 18 | | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 18 | | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 18 | | 3 (0)| 00:00:01 |
|* 9 | HASH JOIN RIGHT OUTER | | 74M| 24G| | 23049 (4)| 00:04:37 |
| 10 | VIEW | | 3992 | 261K| | 6268 (1)| 00:01:16 |
| 11 | HASH GROUP BY | | 3992 | 393K| | 6268 (1)| 00:01:16 |
|* 12 | HASH JOIN OUTER | | 3992 | 393K| | 6267 (1)| 00:01:16 |
|* 13 | HASH JOIN | | 592 | 24272 | | 6032 (1)| 00:01:13 |
| 14 | JOIN FILTER CREATE | :BF0000 | 3 | 45 | | 6 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | F_EXTC_FINANCE_PARA | 3 | 45 | | 6 (0)| 00:00:01 |
| 16 | VIEW | | 102K| 2599K| | 6025 (1)| 00:01:13 |
| 17 | HASH UNIQUE | | 102K| 6999K| 9648K| 6025 (1)| 00:01:13 |
| 18 | JOIN FILTER USE | :BF0000 | 102K| 6999K| | 4334 (1)| 00:00:53 |
|* 19 | TABLE ACCESS FULL | F_EVT_EXTR_FINANCE_BOOK | 102K| 6999K| | 4334 (1)| 00:00:53 |
| 20 | VIEW | V_M_PTY_EMP_INFO | 25128 | 1472K| | 235 (2)| 00:00:03 |
|* 21 | HASH JOIN OUTER | | 25128 | 2404K| | 235 (2)| 00:00:03 |
|* 22 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25128 | 638K| | 107 (1)| 00:00:02 |
| 23 | VIEW | | 25185 | 1770K| | 127 (2)| 00:00:02 |
|* 24 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 127 (2)| 00:00:02 |
|* 25 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 23 | 322 | | 3 (0)| 00:00:01 |
| 26 | VIEW | | 25185 | 1770K| | 124 (2)| 00:00:02 |
|* 27 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 124 (2)| 00:00:02 |
|* 28 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 112 | 1568 | | 3 (0)| 00:00:01 |
|* 29 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
| 30 | VIEW | | 25185 | 1770K| | 120 (1)| 00:00:02 |
|* 31 | HASH JOIN RIGHT OUTER | | 25185 | 1942K| | 120 (1)| 00:00:02 |
|* 32 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 12 | 168 | | 3 (0)| 00:00:01 |
|* 33 | HASH JOIN RIGHT OUTER | | 25185 | 1598K| | 117 (1)| 00:00:02 |
| 34 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 150 | 1050 | | 3 (0)| 00:00:01 |
|* 35 | HASH JOIN RIGHT OUTER | | 25185 | 1426K| | 114 (1)| 00:00:02 |
| 36 | VIEW | M_CBS_TO_DW_ORG | 50 | 1700 | | 7 (0)| 00:00:01 |
| 37 | UNION-ALL | | | | | | |
|* 38 | TABLE ACCESS FULL | F_PTY_ORG | 35 | 770 | | 4 (0)| 00:00:01 |
|* 39 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 15 | 105 | | 3 (0)| 00:00:01 |
|* 40 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25185 | 590K| | 107 (1)| 00:00:02 |
|* 41 | HASH JOIN RIGHT OUTER | | 1523K| 408M| | 16287 (1)| 00:03:16 |
| 42 | VIEW | | 2316 | 151K| | 5424 (1)| 00:01:06 |
| 43 | HASH GROUP BY | | 2316 | 246K| | 5424 (1)| 00:01:06 |
|* 44 | HASH JOIN OUTER | | 2316 | 246K| | 5423 (1)| 00:01:06 |
|* 45 | HASH JOIN | | 343 | 16807 | | 5189 (1)| 00:01:03 |
| 46 | JOIN FILTER CREATE | :BF0001 | 4 | 92 | | 6 (0)| 00:00:01 |
|* 47 | TABLE ACCESS FULL | F_EXTC_FINANCE_PARA | 4 | 92 | | 6 (0)| 00:00:01 |
| 48 | VIEW | | 51195 | 1299K| | 5182 (1)| 00:01:03 |
| 49 | HASH UNIQUE | | 51195 | 3499K| 4832K| 5182 (1)| 00:01:03 |
| 50 | JOIN FILTER USE | :BF0001 | 51195 | 3499K| | 4334 (1)| 00:00:53 |
|* 51 | TABLE ACCESS FULL | F_EVT_EXTR_FINANCE_BOOK | 51195 | 3499K| | 4334 (1)| 00:00:53 |
| 52 | VIEW | V_M_PTY_EMP_INFO | 25128 | 1472K| | 235 (2)| 00:00:03 |
|* 53 | HASH JOIN OUTER | | 25128 | 2404K| | 235 (2)| 00:00:03 |
|* 54 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25128 | 638K| | 107 (1)| 00:00:02 |
| 55 | VIEW | | 25185 | 1770K| | 127 (2)| 00:00:02 |
|* 56 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 127 (2)| 00:00:02 |
|* 57 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 23 | 322 | | 3 (0)| 00:00:01 |
| 58 | VIEW | | 25185 | 1770K| | 124 (2)| 00:00:02 |
|* 59 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 124 (2)| 00:00:02 |
|* 60 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 112 | 1568 | | 3 (0)| 00:00:01 |
|* 61 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
| 62 | VIEW | | 25185 | 1770K| | 120 (1)| 00:00:02 |
|* 63 | HASH JOIN RIGHT OUTER | | 25185 | 1942K| | 120 (1)| 00:00:02 |
|* 64 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 12 | 168 | | 3 (0)| 00:00:01 |
|* 65 | HASH JOIN RIGHT OUTER | | 25185 | 1598K| | 117 (1)| 00:00:02 |
| 66 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 150 | 1050 | | 3 (0)| 00:00:01 |
|* 67 | HASH JOIN RIGHT OUTER | | 25185 | 1426K| | 114 (1)| 00:00:02 |
| 68 | VIEW | M_CBS_TO_DW_ORG | 50 | 1700 | | 7 (0)| 00:00:01 |
| 69 | UNION-ALL | | | | | | |
|* 70 | TABLE ACCESS FULL | F_PTY_ORG | 35 | 770 | | 4 (0)| 00:00:01 |
|* 71 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 15 | 105 | | 3 (0)| 00:00:01 |
|* 72 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25185 | 590K| | 107 (1)| 00:00:02 |
|* 73 | HASH JOIN RIGHT OUTER | | 53946 | 11M| | 10852 (1)| 00:02:11 |
| 74 | VIEW | | 1910 | 124K| | 5424 (1)| 00:01:06 |
| 75 | HASH GROUP BY | | 1910 | 188K| | 5424 (1)| 00:01:06 |
|* 76 | HASH JOIN OUTER | | 1910 | 188K| | 5423 (1)| 00:01:06 |
|* 77 | HASH JOIN | | 283 | 11603 | | 5189 (1)| 00:01:03 |
| 78 | JOIN FILTER CREATE | :BF0002 | 3 | 45 | | 6 (0)| 00:00:01 |
|* 79 | TABLE ACCESS FULL | F_EXTC_FINANCE_PARA | 3 | 45 | | 6 (0)| 00:00:01 |
| 80 | VIEW | | 51195 | 1299K| | 5182 (1)| 00:01:03 |
| 81 | HASH UNIQUE | | 51195 | 3499K| 4832K| 5182 (1)| 00:01:03 |
| 82 | JOIN FILTER USE | :BF0002 | 51195 | 3499K| | 4334 (1)| 00:00:53 |
|* 83 | TABLE ACCESS FULL | F_EVT_EXTR_FINANCE_BOOK | 51195 | 3499K| | 4334 (1)| 00:00:53 |
| 84 | VIEW | V_M_PTY_EMP_INFO | 25128 | 1472K| | 235 (2)| 00:00:03 |
|* 85 | HASH JOIN OUTER | | 25128 | 2404K| | 235 (2)| 00:00:03 |
|* 86 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25128 | 638K| | 107 (1)| 00:00:02 |
| 87 | VIEW | | 25185 | 1770K| | 127 (2)| 00:00:02 |
|* 88 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 127 (2)| 00:00:02 |
|* 89 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 23 | 322 | | 3 (0)| 00:00:01 |
| 90 | VIEW | | 25185 | 1770K| | 124 (2)| 00:00:02 |
|* 91 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 124 (2)| 00:00:02 |
|* 92 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 112 | 1568 | | 3 (0)| 00:00:01 |
|* 93 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
| 94 | VIEW | | 25185 | 1770K| | 120 (1)| 00:00:02 |
|* 95 | HASH JOIN RIGHT OUTER | | 25185 | 1942K| | 120 (1)| 00:00:02 |
|* 96 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 12 | 168 | | 3 (0)| 00:00:01 |
|* 97 | HASH JOIN RIGHT OUTER | | 25185 | 1598K| | 117 (1)| 00:00:02 |
| 98 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 150 | 1050 | | 3 (0)| 00:00:01 |
|* 99 | HASH JOIN RIGHT OUTER | | 25185 | 1426K| | 114 (1)| 00:00:02 |
| 100 | VIEW | M_CBS_TO_DW_ORG | 50 | 1700 | | 7 (0)| 00:00:01 |
| 101 | UNION-ALL | | | | | | |
|*102 | TABLE ACCESS FULL | F_PTY_ORG | 35 | 770 | | 4 (0)| 00:00:01 |
|*103 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 15 | 105 | | 3 (0)| 00:00:01 |
|*104 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25185 | 590K| | 107 (1)| 00:00:02 |
|*105 | HASH JOIN OUTER | | 2316 | 332K| | 5428 (1)| 00:01:06 |
| 106 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 82 | 5494 | | 3 (0)| 00:00:01 |
| 107 | VIEW | | 2316 | 180K| | 5424 (1)| 00:01:06 |
| 108 | HASH GROUP BY | | 2316 | 178K| | 5424 (1)| 00:01:06 |
| 109 | VIEW | VW_DAG_0 | 2316 | 178K| | 5424 (1)| 00:01:06 |
| 110 | HASH GROUP BY | | 2316 | 291K| | 5424 (1)| 00:01:06 |
|*111 | HASH JOIN OUTER | | 2316 | 291K| | 5423 (1)| 00:01:06 |
|*112 | HASH JOIN | | 343 | 23667 | | 5189 (1)| 00:01:03 |
| 113 | JOIN FILTER CREATE | :BF0003 | 4 | 92 | | 6 (0)| 00:00:01 |
|*114 | TABLE ACCESS FULL | F_EXTC_FINANCE_PARA | 4 | 92 | | 6 (0)| 00:00:01 |
| 115 | VIEW | | 51195 | 2299K| | 5182 (1)| 00:01:03 |
| 116 | HASH UNIQUE | | 51195 | 3499K| 4832K| 5182 (1)| 00:01:03 |
| 117 | JOIN FILTER USE | :BF0003 | 51195 | 3499K| | 4334 (1)| 00:00:53 |
|*118 | TABLE ACCESS FULL | F_EVT_EXTR_FINANCE_BOOK | 51195 | 3499K| | 4334 (1)| 00:00:53 |
| 119 | VIEW | V_M_PTY_EMP_INFO | 25128 | 1472K| | 235 (2)| 00:00:03 |
|*120 | HASH JOIN OUTER | | 25128 | 2404K| | 235 (2)| 00:00:03 |
|*121 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25128 | 638K| | 107 (1)| 00:00:02 |
| 122 | VIEW | | 25185 | 1770K| | 127 (2)| 00:00:02 |
|*123 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 127 (2)| 00:00:02 |
|*124 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 23 | 322 | | 3 (0)| 00:00:01 |
| 125 | VIEW | | 25185 | 1770K| | 124 (2)| 00:00:02 |
|*126 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 124 (2)| 00:00:02 |
|*127 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 112 | 1568 | | 3 (0)| 00:00:01 |
|*128 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
| 129 | VIEW | | 25185 | 1770K| | 120 (1)| 00:00:02 |
|*130 | HASH JOIN RIGHT OUTER | | 25185 | 1942K| | 120 (1)| 00:00:02 |
|*131 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 12 | 168 | | 3 (0)| 00:00:01 |
|*132 | HASH JOIN RIGHT OUTER | | 25185 | 1598K| | 117 (1)| 00:00:02 |
| 133 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 150 | 1050 | | 3 (0)| 00:00:01 |
|*134 | HASH JOIN RIGHT OUTER| | 25185 | 1426K| | 114 (1)| 00:00:02 |
| 135 | VIEW | M_CBS_TO_DW_ORG | 50 | 1700 | | 7 (0)| 00:00:01 |
| 136 | UNION-ALL | | | | | | |
|*137 | TABLE ACCESS FULL | F_PTY_ORG | 35 | 770 | | 4 (0)| 00:00:01 |
|*138 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 15 | 105 | | 3 (0)| 00:00:01 |
|*139 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25185 | 590K| | 107 (1)| 00:00:02 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:B1)
2 - filter("ID"=:B1)
3 - filter("ID"=:B1)
4 - filter("ID"=:B1)
5 - filter("ID"=:B1)
6 - filter("ID"=:B1)
7 - filter("ID"=:B1)
8 - filter("ID"=:B1)
9 - access("E"."ORG_ID"="DD"."SEC_ORG_ID"(+))
12 - access("A"."RECOM_OPER_NO"="C"."PTY_ID"(+))
13 - access("A"."PRODUCT_NO"="B"."PRODUCT_NO")
15 - filter("B"."FREEZE_END_DATE"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."FREEZE_END_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
19 - filter("A"."VALID_FLAG"='1' AND "A"."DEDUCT_FLAG"='1' AND "A"."ACCT_FLAG"<>'0' AND
SYS_OP_BLOOM_FILTER(:BF0000,"A"."PRODUCT_NO"))
21 - access("T"."START_DT"="B"."START_DT"(+) AND "T"."SOURCE_CODE"="B"."SOURCE_CODE"(+) AND
"T"."PTY_ID"="B"."PTY_ID"(+))
22 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."END_DT">TO_DATE('
2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
24 - access("FST"."SEC_ORG_ID"="ORG_ID"(+))
25 - filter("ORG_LEVEL"(+)=2)
27 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID"))
28 - filter("ORG_LEVEL"(+)=3)
29 - filter("ID"=:B1)
31 - access("C"."ORG_ID"="ORG_ID"(+))
32 - filter("ORG_LEVEL"(+)=4)
33 - access("B"."ORG_ID"="C"."ORG_ID"(+))
35 - access("A"."ORG_NO"="B"."PTY_ID"(+))
38 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
39 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%'
AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL)
40 - filter("A"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
41 - access("E"."ORG_ID"="EE"."SEC_ORG_ID"(+))
44 - access("A"."RECOM_OPER_NO"="C"."PTY_ID"(+))
45 - access("A"."PRODUCT_NO"="B"."PRODUCT_NO")
47 - filter("B"."FREEZE_START_DATE"<TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."FREEZE_END_DATE">TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
51 - filter("A"."ACCT_FLAG"='0' AND "A"."VALID_FLAG"='1' AND "A"."DEDUCT_FLAG"='1' AND
SYS_OP_BLOOM_FILTER(:BF0001,"A"."PRODUCT_NO"))
53 - access("T"."START_DT"="B"."START_DT"(+) AND "T"."SOURCE_CODE"="B"."SOURCE_CODE"(+) AND
"T"."PTY_ID"="B"."PTY_ID"(+))
54 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."END_DT">TO_DATE('
2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
56 - access("FST"."SEC_ORG_ID"="ORG_ID"(+))
57 - filter("ORG_LEVEL"(+)=2)
59 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID"))
60 - filter("ORG_LEVEL"(+)=3)
61 - filter("ID"=:B1)
63 - access("C"."ORG_ID"="ORG_ID"(+))
64 - filter("ORG_LEVEL"(+)=4)
65 - access("B"."ORG_ID"="C"."ORG_ID"(+))
67 - access("A"."ORG_NO"="B"."PTY_ID"(+))
70 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
71 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%'
AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL)
72 - filter("A"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
73 - access("E"."ORG_ID"="AA"."SEC_ORG_ID"(+))
76 - access("A"."RECOM_OPER_NO"="C"."PTY_ID"(+))
77 - access("A"."PRODUCT_NO"="B"."PRODUCT_NO")
79 - filter("B"."FREEZE_START_DATE"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."FREEZE_START_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
83 - filter("A"."ACCT_FLAG"='0' AND "A"."VALID_FLAG"='1' AND "A"."DEDUCT_FLAG"='1' AND
SYS_OP_BLOOM_FILTER(:BF0002,"A"."PRODUCT_NO"))
85 - access("T"."START_DT"="B"."START_DT"(+) AND "T"."SOURCE_CODE"="B"."SOURCE_CODE"(+) AND
"T"."PTY_ID"="B"."PTY_ID"(+))
86 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."END_DT">TO_DATE('
2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
88 - access("FST"."SEC_ORG_ID"="ORG_ID"(+))
89 - filter("ORG_LEVEL"(+)=2)
91 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID"))
92 - filter("ORG_LEVEL"(+)=3)
93 - filter("ID"=:B1)
95 - access("C"."ORG_ID"="ORG_ID"(+))
96 - filter("ORG_LEVEL"(+)=4)
97 - access("B"."ORG_ID"="C"."ORG_ID"(+))
99 - access("A"."ORG_NO"="B"."PTY_ID"(+))
102 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
103 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%'
AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL)
104 - filter("A"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
105 - access("E"."ORG_ID"="BB"."SEC_ORG_ID"(+))
111 - access("A"."RECOM_OPER_NO"="C"."PTY_ID"(+))
112 - access("A"."PRODUCT_NO"="B"."PRODUCT_NO")
114 - filter("B"."FREEZE_START_DATE"<TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."FREEZE_END_DATE">TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
118 - filter("A"."ACCT_FLAG"='0' AND "A"."VALID_FLAG"='1' AND "A"."DEDUCT_FLAG"='1' AND
SYS_OP_BLOOM_FILTER(:BF0003,"A"."PRODUCT_NO"))
120 - access("T"."START_DT"="B"."START_DT"(+) AND "T"."SOURCE_CODE"="B"."SOURCE_CODE"(+) AND
"T"."PTY_ID"="B"."PTY_ID"(+))
121 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."END_DT">TO_DATE('
2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
123 - access("FST"."SEC_ORG_ID"="ORG_ID"(+))
124 - filter("ORG_LEVEL"(+)=2)
126 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID"))
127 - filter("ORG_LEVEL"(+)=3)
128 - filter("ID"=:B1)
130 - access("C"."ORG_ID"="ORG_ID"(+))
131 - filter("ORG_LEVEL"(+)=4)
132 - access("B"."ORG_ID"="C"."ORG_ID"(+))
134 - access("A"."ORG_NO"="B"."PTY_ID"(+))
137 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
138 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%'
AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL)
139 - filter("A"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
--------------------------
使用hints no_push_pred(c)
explain plan for
select e.* ,
aa.* ,
bb.* ,
ee.* ,
dd.*
from b_m_sys_branch e
left join
--购买金额
(select /*+ no_push_pred(c) */ nvl(c.SEC_ORG_ID, '其他') as SEC_ORG_ID,
max(nvl(c.SEC_ORG_NAME, '其他')) as SEC_ORG_NAME,
sum(a.finance_amt) as 购买金额
from (SELECT DISTINCT a.RECOM_OPER_NO,
a.cust_no,
a.cust_name,
a.product_no,
a.contract_no,
a.finance_amt,
a.sign_date
FROM DWF.F_EVT_EXTR_FINANCE_BOOK a
where a.valid_flag = '1' --有效标志
AND a.deduct_flag = '1' -- 扣款标志
AND a.acct_flag = '0') a
left join dwf.f_extc_finance_para b
on a.product_no = b.product_no
left join dwm.v_m_pty_emp_info c
on a.recom_oper_no = c.PTY_ID
and c.START_DT <= to_date('2014-03-21', 'YYYY-MM-DD')
and c.END_DT > to_date('2014-03-21', 'YYYY-MM-DD')
where b.freeze_start_date >= to_date('2014-01-01', 'YYYY-MM-DD')
and b.freeze_start_date <= to_date('2014-03-21', 'YYYY-MM-DD')
group by c.SEC_ORG_ID) aa
on e.org_id = aa.SEC_ORG_ID
left join (
--存量数
select /*+ no_push_pred(c) */ nvl(c.SEC_ORG_ID, '其他') as SEC_ORG_ID,
max(nvl(c.SEC_ORG_NAME, '其他')) as SEC_ORG_NAME,
count(distinct a.cust_no) as 存量客户数,
sum(a.finance_amt) as 存量金额
from (SELECT DISTINCT a.RECOM_OPER_NO,
a.cust_no,
a.cust_name,
a.product_no,
a.contract_no,
a.finance_amt,
a.sign_date
FROM DWF.F_EVT_EXTR_FINANCE_BOOK a
where a.valid_flag = '1' --有效标志
AND a.deduct_flag = '1' -- 扣款标志
AND a.acct_flag = '0') a
left join dwf.f_extc_finance_para b
on a.product_no = b.product_no
left join dwm.v_m_pty_emp_info c
on a.RECOM_OPER_NO = c.PTY_ID
and c.START_DT <= to_date('2014-03-21', 'YYYY-MM-DD')
and c.END_DT > to_date('2014-03-21', 'YYYY-MM-DD')
where b.freeze_start_date < to_date('2014-03-21', 'YYYY-MM-DD')
and b.freeze_end_date > to_date('2014-03-21', 'YYYY-MM-DD')
group by c.SEC_ORG_ID) bb
on e.org_id = bb.SEC_ORG_ID
left join (
--存量数日均
select /*+ no_push_pred(c) */ nvl(c.SEC_ORG_ID, '其他') as SEC_ORG_ID,
max(nvl(c.SEC_ORG_NAME, '其他')) as SEC_ORG_NAME,
sum(case
when b.freeze_start_date >=
to_date('2014-01-01',
'YYYY-MM-DD') then
(to_date('2014-03-21',
'YYYY-MM-DD') -
b.freeze_start_date ) *
a.finance_amt / 365
when b.freeze_start_date <
to_date('2014-01-01',
'YYYY-MM-DD') then
(to_date('2014-03-21',
'YYYY-MM-DD') - to_date('2014-01-01',
'YYYY-MM-DD')
) *
a.finance_amt / 365
end) 存量日均
from (SELECT DISTINCT a.RECOM_OPER_NO,
a.cust_no,
a.cust_name,
a.product_no,
a.contract_no,
a.finance_amt,
a.sign_date
FROM DWF.F_EVT_EXTR_FINANCE_BOOK a
where a.valid_flag = '1' --有效标志
AND a.deduct_flag = '1' -- 扣款标志
AND a.acct_flag = '0') a
left join dwf.f_extc_finance_para b
on a.product_no = b.product_no
left join dwm.v_m_pty_emp_info c
on a.RECOM_OPER_NO = c.PTY_ID
and c.START_DT <= to_date('2014-03-21', 'YYYY-MM-DD')
and c.END_DT > to_date('2014-03-21', 'YYYY-MM-DD')
where b.freeze_start_date < to_date('2014-03-21', 'YYYY-MM-DD')
and b.freeze_end_date > to_date('2014-03-21', 'YYYY-MM-DD')
group by c.SEC_ORG_ID) ee
on e.org_id = ee.SEC_ORG_ID
left join (
--到期金额
select /*+ no_push_pred(c) */ nvl(c.SEC_ORG_ID, '其他') as SEC_ORG_ID,
max(nvl(c.SEC_ORG_NAME, '其他')) as SEC_ORG_NAME,
sum(a.finance_amt) as 到期金额
from (SELECT DISTINCT a.RECOM_OPER_NO,
a.cust_no,
a.cust_name,
a.product_no,
a.contract_no,
a.finance_amt,
a.sign_date
FROM DWF.F_EVT_EXTR_FINANCE_BOOK a
where a.valid_flag = '1' --有效标志
AND a.deduct_flag = '1' -- 扣款标志
AND a.acct_flag <> '0'
) a
left join dwf.f_extc_finance_para b
on a.product_no = b.product_no
left join dwm.v_m_pty_emp_info c
on a.RECOM_OPER_NO = c.PTY_ID
and c.START_DT <= to_date('2014-03-21', 'YYYY-MM-DD')
and c.END_DT > to_date('2014-03-21', 'YYYY-MM-DD')
where b.freeze_end_date >= to_date('2014-01-01', 'YYYY-MM-DD')
and b.freeze_end_date <= to_date('2014-03-21', 'YYYY-MM-DD')
group by c.SEC_ORG_ID) dd
on e.org_id = dd.SEC_ORG_ID ;
select * from table(dbms_xplan.display()) ;
Plan hash value: 3624608596
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74M| 24G| | 23049 (4)| 00:04:37 |
|* 1 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 18 | | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 18 | | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 18 | | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 18 | | 3 (0)| 00:00:01 |
|* 9 | HASH JOIN RIGHT OUTER | | 74M| 24G| | 23049 (4)| 00:04:37 |
| 10 | VIEW | | 3992 | 261K| | 6268 (1)| 00:01:16 |
| 11 | HASH GROUP BY | | 3992 | 393K| | 6268 (1)| 00:01:16 |
|* 12 | HASH JOIN OUTER | | 3992 | 393K| | 6267 (1)| 00:01:16 |
|* 13 | HASH JOIN | | 592 | 24272 | | 6032 (1)| 00:01:13 |
| 14 | JOIN FILTER CREATE | :BF0000 | 3 | 45 | | 6 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | F_EXTC_FINANCE_PARA | 3 | 45 | | 6 (0)| 00:00:01 |
| 16 | VIEW | | 102K| 2599K| | 6025 (1)| 00:01:13 |
| 17 | HASH UNIQUE | | 102K| 6999K| 9648K| 6025 (1)| 00:01:13 |
| 18 | JOIN FILTER USE | :BF0000 | 102K| 6999K| | 4334 (1)| 00:00:53 |
|* 19 | TABLE ACCESS FULL | F_EVT_EXTR_FINANCE_BOOK | 102K| 6999K| | 4334 (1)| 00:00:53 |
| 20 | VIEW | V_M_PTY_EMP_INFO | 25128 | 1472K| | 235 (2)| 00:00:03 |
|* 21 | HASH JOIN OUTER | | 25128 | 2404K| | 235 (2)| 00:00:03 |
|* 22 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25128 | 638K| | 107 (1)| 00:00:02 |
| 23 | VIEW | | 25185 | 1770K| | 127 (2)| 00:00:02 |
|* 24 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 127 (2)| 00:00:02 |
|* 25 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 23 | 322 | | 3 (0)| 00:00:01 |
| 26 | VIEW | | 25185 | 1770K| | 124 (2)| 00:00:02 |
|* 27 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 124 (2)| 00:00:02 |
|* 28 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 112 | 1568 | | 3 (0)| 00:00:01 |
|* 29 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
| 30 | VIEW | | 25185 | 1770K| | 120 (1)| 00:00:02 |
|* 31 | HASH JOIN RIGHT OUTER | | 25185 | 1942K| | 120 (1)| 00:00:02 |
|* 32 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 12 | 168 | | 3 (0)| 00:00:01 |
|* 33 | HASH JOIN RIGHT OUTER | | 25185 | 1598K| | 117 (1)| 00:00:02 |
| 34 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 150 | 1050 | | 3 (0)| 00:00:01 |
|* 35 | HASH JOIN RIGHT OUTER | | 25185 | 1426K| | 114 (1)| 00:00:02 |
| 36 | VIEW | M_CBS_TO_DW_ORG | 50 | 1700 | | 7 (0)| 00:00:01 |
| 37 | UNION-ALL | | | | | | |
|* 38 | TABLE ACCESS FULL | F_PTY_ORG | 35 | 770 | | 4 (0)| 00:00:01 |
|* 39 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 15 | 105 | | 3 (0)| 00:00:01 |
|* 40 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25185 | 590K| | 107 (1)| 00:00:02 |
|* 41 | HASH JOIN RIGHT OUTER | | 1523K| 408M| | 16287 (1)| 00:03:16 |
| 42 | VIEW | | 2316 | 151K| | 5424 (1)| 00:01:06 |
| 43 | HASH GROUP BY | | 2316 | 246K| | 5424 (1)| 00:01:06 |
|* 44 | HASH JOIN OUTER | | 2316 | 246K| | 5423 (1)| 00:01:06 |
|* 45 | HASH JOIN | | 343 | 16807 | | 5189 (1)| 00:01:03 |
| 46 | JOIN FILTER CREATE | :BF0001 | 4 | 92 | | 6 (0)| 00:00:01 |
|* 47 | TABLE ACCESS FULL | F_EXTC_FINANCE_PARA | 4 | 92 | | 6 (0)| 00:00:01 |
| 48 | VIEW | | 51195 | 1299K| | 5182 (1)| 00:01:03 |
| 49 | HASH UNIQUE | | 51195 | 3499K| 4832K| 5182 (1)| 00:01:03 |
| 50 | JOIN FILTER USE | :BF0001 | 51195 | 3499K| | 4334 (1)| 00:00:53 |
|* 51 | TABLE ACCESS FULL | F_EVT_EXTR_FINANCE_BOOK | 51195 | 3499K| | 4334 (1)| 00:00:53 |
| 52 | VIEW | V_M_PTY_EMP_INFO | 25128 | 1472K| | 235 (2)| 00:00:03 |
|* 53 | HASH JOIN OUTER | | 25128 | 2404K| | 235 (2)| 00:00:03 |
|* 54 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25128 | 638K| | 107 (1)| 00:00:02 |
| 55 | VIEW | | 25185 | 1770K| | 127 (2)| 00:00:02 |
|* 56 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 127 (2)| 00:00:02 |
|* 57 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 23 | 322 | | 3 (0)| 00:00:01 |
| 58 | VIEW | | 25185 | 1770K| | 124 (2)| 00:00:02 |
|* 59 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 124 (2)| 00:00:02 |
|* 60 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 112 | 1568 | | 3 (0)| 00:00:01 |
|* 61 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
| 62 | VIEW | | 25185 | 1770K| | 120 (1)| 00:00:02 |
|* 63 | HASH JOIN RIGHT OUTER | | 25185 | 1942K| | 120 (1)| 00:00:02 |
|* 64 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 12 | 168 | | 3 (0)| 00:00:01 |
|* 65 | HASH JOIN RIGHT OUTER | | 25185 | 1598K| | 117 (1)| 00:00:02 |
| 66 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 150 | 1050 | | 3 (0)| 00:00:01 |
|* 67 | HASH JOIN RIGHT OUTER | | 25185 | 1426K| | 114 (1)| 00:00:02 |
| 68 | VIEW | M_CBS_TO_DW_ORG | 50 | 1700 | | 7 (0)| 00:00:01 |
| 69 | UNION-ALL | | | | | | |
|* 70 | TABLE ACCESS FULL | F_PTY_ORG | 35 | 770 | | 4 (0)| 00:00:01 |
|* 71 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 15 | 105 | | 3 (0)| 00:00:01 |
|* 72 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25185 | 590K| | 107 (1)| 00:00:02 |
|* 73 | HASH JOIN RIGHT OUTER | | 53946 | 11M| | 10852 (1)| 00:02:11 |
| 74 | VIEW | | 1910 | 124K| | 5424 (1)| 00:01:06 |
| 75 | HASH GROUP BY | | 1910 | 188K| | 5424 (1)| 00:01:06 |
|* 76 | HASH JOIN OUTER | | 1910 | 188K| | 5423 (1)| 00:01:06 |
|* 77 | HASH JOIN | | 283 | 11603 | | 5189 (1)| 00:01:03 |
| 78 | JOIN FILTER CREATE | :BF0002 | 3 | 45 | | 6 (0)| 00:00:01 |
|* 79 | TABLE ACCESS FULL | F_EXTC_FINANCE_PARA | 3 | 45 | | 6 (0)| 00:00:01 |
| 80 | VIEW | | 51195 | 1299K| | 5182 (1)| 00:01:03 |
| 81 | HASH UNIQUE | | 51195 | 3499K| 4832K| 5182 (1)| 00:01:03 |
| 82 | JOIN FILTER USE | :BF0002 | 51195 | 3499K| | 4334 (1)| 00:00:53 |
|* 83 | TABLE ACCESS FULL | F_EVT_EXTR_FINANCE_BOOK | 51195 | 3499K| | 4334 (1)| 00:00:53 |
| 84 | VIEW | V_M_PTY_EMP_INFO | 25128 | 1472K| | 235 (2)| 00:00:03 |
|* 85 | HASH JOIN OUTER | | 25128 | 2404K| | 235 (2)| 00:00:03 |
|* 86 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25128 | 638K| | 107 (1)| 00:00:02 |
| 87 | VIEW | | 25185 | 1770K| | 127 (2)| 00:00:02 |
|* 88 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 127 (2)| 00:00:02 |
|* 89 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 23 | 322 | | 3 (0)| 00:00:01 |
| 90 | VIEW | | 25185 | 1770K| | 124 (2)| 00:00:02 |
|* 91 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 124 (2)| 00:00:02 |
|* 92 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 112 | 1568 | | 3 (0)| 00:00:01 |
|* 93 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
| 94 | VIEW | | 25185 | 1770K| | 120 (1)| 00:00:02 |
|* 95 | HASH JOIN RIGHT OUTER | | 25185 | 1942K| | 120 (1)| 00:00:02 |
|* 96 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 12 | 168 | | 3 (0)| 00:00:01 |
|* 97 | HASH JOIN RIGHT OUTER | | 25185 | 1598K| | 117 (1)| 00:00:02 |
| 98 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 150 | 1050 | | 3 (0)| 00:00:01 |
|* 99 | HASH JOIN RIGHT OUTER | | 25185 | 1426K| | 114 (1)| 00:00:02 |
| 100 | VIEW | M_CBS_TO_DW_ORG | 50 | 1700 | | 7 (0)| 00:00:01 |
| 101 | UNION-ALL | | | | | | |
|*102 | TABLE ACCESS FULL | F_PTY_ORG | 35 | 770 | | 4 (0)| 00:00:01 |
|*103 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 15 | 105 | | 3 (0)| 00:00:01 |
|*104 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25185 | 590K| | 107 (1)| 00:00:02 |
|*105 | HASH JOIN OUTER | | 2316 | 332K| | 5428 (1)| 00:01:06 |
| 106 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 82 | 5494 | | 3 (0)| 00:00:01 |
| 107 | VIEW | | 2316 | 180K| | 5424 (1)| 00:01:06 |
| 108 | HASH GROUP BY | | 2316 | 178K| | 5424 (1)| 00:01:06 |
| 109 | VIEW | VW_DAG_0 | 2316 | 178K| | 5424 (1)| 00:01:06 |
| 110 | HASH GROUP BY | | 2316 | 291K| | 5424 (1)| 00:01:06 |
|*111 | HASH JOIN OUTER | | 2316 | 291K| | 5423 (1)| 00:01:06 |
|*112 | HASH JOIN | | 343 | 23667 | | 5189 (1)| 00:01:03 |
| 113 | JOIN FILTER CREATE | :BF0003 | 4 | 92 | | 6 (0)| 00:00:01 |
|*114 | TABLE ACCESS FULL | F_EXTC_FINANCE_PARA | 4 | 92 | | 6 (0)| 00:00:01 |
| 115 | VIEW | | 51195 | 2299K| | 5182 (1)| 00:01:03 |
| 116 | HASH UNIQUE | | 51195 | 3499K| 4832K| 5182 (1)| 00:01:03 |
| 117 | JOIN FILTER USE | :BF0003 | 51195 | 3499K| | 4334 (1)| 00:00:53 |
|*118 | TABLE ACCESS FULL | F_EVT_EXTR_FINANCE_BOOK | 51195 | 3499K| | 4334 (1)| 00:00:53 |
| 119 | VIEW | V_M_PTY_EMP_INFO | 25128 | 1472K| | 235 (2)| 00:00:03 |
|*120 | HASH JOIN OUTER | | 25128 | 2404K| | 235 (2)| 00:00:03 |
|*121 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25128 | 638K| | 107 (1)| 00:00:02 |
| 122 | VIEW | | 25185 | 1770K| | 127 (2)| 00:00:02 |
|*123 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 127 (2)| 00:00:02 |
|*124 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 23 | 322 | | 3 (0)| 00:00:01 |
| 125 | VIEW | | 25185 | 1770K| | 124 (2)| 00:00:02 |
|*126 | HASH JOIN RIGHT OUTER | | 25185 | 2115K| | 124 (2)| 00:00:02 |
|*127 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 112 | 1568 | | 3 (0)| 00:00:01 |
|*128 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | | 3 (0)| 00:00:01 |
| 129 | VIEW | | 25185 | 1770K| | 120 (1)| 00:00:02 |
|*130 | HASH JOIN RIGHT OUTER | | 25185 | 1942K| | 120 (1)| 00:00:02 |
|*131 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 12 | 168 | | 3 (0)| 00:00:01 |
|*132 | HASH JOIN RIGHT OUTER | | 25185 | 1598K| | 117 (1)| 00:00:02 |
| 133 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 150 | 1050 | | 3 (0)| 00:00:01 |
|*134 | HASH JOIN RIGHT OUTER| | 25185 | 1426K| | 114 (1)| 00:00:02 |
| 135 | VIEW | M_CBS_TO_DW_ORG | 50 | 1700 | | 7 (0)| 00:00:01 |
| 136 | UNION-ALL | | | | | | |
|*137 | TABLE ACCESS FULL | F_PTY_ORG | 35 | 770 | | 4 (0)| 00:00:01 |
|*138 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 15 | 105 | | 3 (0)| 00:00:01 |
|*139 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25185 | 590K| | 107 (1)| 00:00:02 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:B1)
2 - filter("ID"=:B1)
3 - filter("ID"=:B1)
4 - filter("ID"=:B1)
5 - filter("ID"=:B1)
6 - filter("ID"=:B1)
7 - filter("ID"=:B1)
8 - filter("ID"=:B1)
9 - access("E"."ORG_ID"="DD"."SEC_ORG_ID"(+))
12 - access("A"."RECOM_OPER_NO"="C"."PTY_ID"(+))
13 - access("A"."PRODUCT_NO"="B"."PRODUCT_NO")
15 - filter("B"."FREEZE_END_DATE"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."FREEZE_END_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
19 - filter("A"."VALID_FLAG"='1' AND "A"."DEDUCT_FLAG"='1' AND "A"."ACCT_FLAG"<>'0' AND
SYS_OP_BLOOM_FILTER(:BF0000,"A"."PRODUCT_NO"))
21 - access("T"."START_DT"="B"."START_DT"(+) AND "T"."SOURCE_CODE"="B"."SOURCE_CODE"(+) AND
"T"."PTY_ID"="B"."PTY_ID"(+))
22 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."END_DT">TO_DATE('
2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
24 - access("FST"."SEC_ORG_ID"="ORG_ID"(+))
25 - filter("ORG_LEVEL"(+)=2)
27 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID"))
28 - filter("ORG_LEVEL"(+)=3)
29 - filter("ID"=:B1)
31 - access("C"."ORG_ID"="ORG_ID"(+))
32 - filter("ORG_LEVEL"(+)=4)
33 - access("B"."ORG_ID"="C"."ORG_ID"(+))
35 - access("A"."ORG_NO"="B"."PTY_ID"(+))
38 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
39 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%'
AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL)
40 - filter("A"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
41 - access("E"."ORG_ID"="EE"."SEC_ORG_ID"(+))
44 - access("A"."RECOM_OPER_NO"="C"."PTY_ID"(+))
45 - access("A"."PRODUCT_NO"="B"."PRODUCT_NO")
47 - filter("B"."FREEZE_START_DATE"<TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."FREEZE_END_DATE">TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
51 - filter("A"."ACCT_FLAG"='0' AND "A"."VALID_FLAG"='1' AND "A"."DEDUCT_FLAG"='1' AND
SYS_OP_BLOOM_FILTER(:BF0001,"A"."PRODUCT_NO"))
53 - access("T"."START_DT"="B"."START_DT"(+) AND "T"."SOURCE_CODE"="B"."SOURCE_CODE"(+) AND
"T"."PTY_ID"="B"."PTY_ID"(+))
54 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."END_DT">TO_DATE('
2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
56 - access("FST"."SEC_ORG_ID"="ORG_ID"(+))
57 - filter("ORG_LEVEL"(+)=2)
59 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID"))
60 - filter("ORG_LEVEL"(+)=3)
61 - filter("ID"=:B1)
63 - access("C"."ORG_ID"="ORG_ID"(+))
64 - filter("ORG_LEVEL"(+)=4)
65 - access("B"."ORG_ID"="C"."ORG_ID"(+))
67 - access("A"."ORG_NO"="B"."PTY_ID"(+))
70 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
71 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%'
AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL)
72 - filter("A"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
73 - access("E"."ORG_ID"="AA"."SEC_ORG_ID"(+))
76 - access("A"."RECOM_OPER_NO"="C"."PTY_ID"(+))
77 - access("A"."PRODUCT_NO"="B"."PRODUCT_NO")
79 - filter("B"."FREEZE_START_DATE"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."FREEZE_START_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
83 - filter("A"."ACCT_FLAG"='0' AND "A"."VALID_FLAG"='1' AND "A"."DEDUCT_FLAG"='1' AND
SYS_OP_BLOOM_FILTER(:BF0002,"A"."PRODUCT_NO"))
85 - access("T"."START_DT"="B"."START_DT"(+) AND "T"."SOURCE_CODE"="B"."SOURCE_CODE"(+) AND
"T"."PTY_ID"="B"."PTY_ID"(+))
86 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."END_DT">TO_DATE('
2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
88 - access("FST"."SEC_ORG_ID"="ORG_ID"(+))
89 - filter("ORG_LEVEL"(+)=2)
91 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID"))
92 - filter("ORG_LEVEL"(+)=3)
93 - filter("ID"=:B1)
95 - access("C"."ORG_ID"="ORG_ID"(+))
96 - filter("ORG_LEVEL"(+)=4)
97 - access("B"."ORG_ID"="C"."ORG_ID"(+))
99 - access("A"."ORG_NO"="B"."PTY_ID"(+))
102 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
103 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%'
AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL)
104 - filter("A"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
105 - access("E"."ORG_ID"="BB"."SEC_ORG_ID"(+))
111 - access("A"."RECOM_OPER_NO"="C"."PTY_ID"(+))
112 - access("A"."PRODUCT_NO"="B"."PRODUCT_NO")
114 - filter("B"."FREEZE_START_DATE"<TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"B"."FREEZE_END_DATE">TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
118 - filter("A"."ACCT_FLAG"='0' AND "A"."VALID_FLAG"='1' AND "A"."DEDUCT_FLAG"='1' AND
SYS_OP_BLOOM_FILTER(:BF0003,"A"."PRODUCT_NO"))
120 - access("T"."START_DT"="B"."START_DT"(+) AND "T"."SOURCE_CODE"="B"."SOURCE_CODE"(+) AND
"T"."PTY_ID"="B"."PTY_ID"(+))
121 - filter("T"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."END_DT">TO_DATE('
2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
123 - access("FST"."SEC_ORG_ID"="ORG_ID"(+))
124 - filter("ORG_LEVEL"(+)=2)
126 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID"))
127 - filter("ORG_LEVEL"(+)=3)
128 - filter("ID"=:B1)
130 - access("C"."ORG_ID"="ORG_ID"(+))
131 - filter("ORG_LEVEL"(+)=4)
132 - access("B"."ORG_ID"="C"."ORG_ID"(+))
134 - access("A"."ORG_NO"="B"."PTY_ID"(+))
137 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
138 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%'
AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL)
139 - filter("A"."START_DT"<=TO_DATE(' 2014-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))