query getting long run and not giving result

User_ROTJD

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

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

Jason_(A_Non)

Please close this question here as it is duplicated over at https://community.oracle.com/tech/developers/discussion/4498708/im-using-oracle-12c-select-query-executing-faster-but-insert-is-getting-longer#latest