im using oracle 12 plsql developer
this query execution getting long executing mote than 6days. i thinks its getting infinite loop.
we are doing consolidating the value by using column are mentioned in connect by clause. based on the hierarchy we took rank1 rows. basically we reducing the number of rows. this query is giving the result in 30sec for records more than 40lakhs. but I'm facing issue when trying to execute attached data there is 972 rows available. please help me find out the issue . (xl file
temp_final_consolidated_1.xlsx (166.69 KB) attached which is pertains to table temp_final_consolidated_1)
temp_final_consolidated_1.xlsx
Uploaded 4:40 pm166.69 KB
with temp_fc as
(select /*+PARALLEL (C,6)*/
C.*
from temp_final_consolidated_1 C )
SELECT f.*, f.new_to_age new_to_age1
FROM (SELECT e.*,
row_number() over(PARTITION BY product_code, brokerage_type, report_description, date_flag_for_rate_selection, folio_no || purch_id || cons_code || brok_dlr_code || brok_dlr_catg, asset_class || sub_fund_code || scheme_code, source_asset_class || source_sub_fund_code || source_scheme_code, sys_regn_to_date, sys_regn_from_date, trade_date_to, trade_date_from, ter_location, transaction_head, sub_type, from_amount, to_amount, brokerage_rate, new_from_age, new_to_age || adj_advance_upfront ORDER BY product_code, brokerage_type, report_description, new_from_age, new_to_age) rank1
FROM (SELECT MIN(d.from_age) over(PARTITION BY product_code, brokerage_type, report_description, date_flag_for_rate_selection, folio_no || purch_id || cons_code || brok_dlr_code || brok_dlr_catg, asset_class || sub_fund_code || scheme_code, source_asset_class || source_sub_fund_code || source_scheme_code, sys_regn_to_date, sys_regn_from_date, trade_date_to, trade_date_from, ter_location, transaction_head, sub_type, from_amount, to_amount, brokerage_rate, new_to_age || adj_advance_upfront ORDER BY product_code, brokerage_type, report_description, new_to_age) new_from_age,
d.*
FROM (SELECT MAX(to_age) over(PARTITION BY product_code, brokerage_type, report_description, date_flag_for_rate_selection, folio_no || purch_id || cons_code || brok_dlr_code || brok_dlr_catg, asset_class || sub_fund_code || scheme_code, source_asset_class || source_sub_fund_code || source_scheme_code, sys_regn_to_date, sys_regn_from_date, trade_date_to, c.trade_date_from, ter_location, transaction_head, sub_type, from_amount, to_amount, brokerage_rate, c.root_from_age || adj_advance_upfront ORDER BY root_from_age) new_to_age,
c.*
FROM (SELECT /*+PARALLEL (C,6)*/
c.*, connect_by_root from_age AS root_from_age
FROM temp_fc c
CONNECT BY prior to_age+1 = from_age and
PRIOR nvl(brokerage_rate, 0) =
nvl(brokerage_rate, 0)
AND PRIOR nvl(brokerage_type, '$$') =
nvl(brokerage_type, '$$')
AND PRIOR nvl(product_code, '$$') =
nvl(product_code, '$$')
AND PRIOR nvl(report_description, '$$') =
nvl(report_description, '$$')
AND PRIOR nvl(folio_no, '$$') =
nvl(folio_no, '$$')
AND PRIOR
nvl(purch_id, 0) = nvl(purch_id, 0)
AND PRIOR nvl(cons_code, '$$') =
nvl(cons_code, '$$')
AND PRIOR nvl(brok_dlr_code, '$$') =
nvl(brok_dlr_code, '$$')
AND PRIOR nvl(brok_dlr_catg, '$$') =
nvl(brok_dlr_catg, '$$')
AND PRIOR nvl(asset_class, '$$') =
nvl(asset_class, '$$')
AND PRIOR nvl(sub_fund_code, '$$') =
nvl(sub_fund_code, '$$')
AND PRIOR nvl(scheme_code, '$$') =
nvl(scheme_code, '$$')
AND PRIOR nvl(source_asset_class, '$$') =
nvl(source_asset_class, '$$')
AND PRIOR nvl(source_sub_fund_code, '$$') =
nvl(source_sub_fund_code, '$$')
AND PRIOR nvl(source_scheme_code, '$$') =
nvl(source_scheme_code, '$$')
AND PRIOR nvl(load_basis, '$$') =
nvl(load_basis, '$$')
AND PRIOR nvl(fee_aging, '$$') =
nvl(fee_aging, '$$')
AND PRIOR nvl(transaction_head, '$$') =
nvl(transaction_head, '$$')
AND PRIOR nvl(sub_type, '$$') =
nvl(sub_type, '$$')
AND PRIOR
nvl(date_flag_for_rate_selection,
'$$') = nvl(date_flag_for_rate_selection,
'$$')
AND PRIOR nvl(sys_regn_from_date,
'31-Dec-2999') =
nvl(sys_regn_from_date,
'31-Dec-2999')
AND PRIOR
nvl(sys_regn_to_date, '31-Dec-2999') =
nvl(sys_regn_to_date, '31-Dec-2999')
AND PRIOR
nvl(trade_date_from, '31-Dec-2999') =
nvl(trade_date_from, '31-Dec-2999')
AND PRIOR
nvl(trade_date_to, '31-Dec-2999') =
nvl(trade_date_to, '31-Dec-2999')
AND PRIOR nvl(ter_location, '$$') =
nvl(ter_location, '$$')
AND PRIOR nvl(from_amount, 0) =
nvl(from_amount, 0)
AND PRIOR nvl(to_amount, 0) =
nvl(to_amount, 0)
AND PRIOR nvl(tf_valuation_basis, '$$') =
nvl(tf_valuation_basis, '$$')
ORDER BY from\_age,
to\_age,brokerage\_rate,
folio\_no,
purch\_id,
cons\_code,
brok\_dlr\_code,
brok\_dlr\_catg,
asset\_class,
sub\_fund\_code,
scheme\_code,
source\_asset\_class,
source\_sub\_fund\_code,
source\_scheme\_code,
load\_basis,
fee\_aging,
transaction\_head,
sub\_type,
date\_flag\_for\_rate\_selection,
sys\_regn\_from\_date,
sys\_regn\_to\_date,
trade\_date\_from,
trade\_date\_to,
ter\_location,
from\_amount,
tf\_valuation\_basis) c) d) e) f
WHERE rank1 = 1