Home » RDBMS Server » Performance Tuning » SQL Query Help (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi)
| SQL Query Help [message #464608] |
Thu, 08 July 2010 08:39  |
marlon_loyolite
Messages: 66
Registered: July 2006
|
Member |
|
|
Hi Friends,
My query formation is like below..
*Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi*
SELECT T1.COL1,T1.COL2,
T1.COL3,T2.COL2,
T3.COL1,T3.COL2,
T4.COL1,T4.COL3,
<CASE statements and calculations results - Some Amount1>,
<CASE statements and calculations results - Some Amount2>
CASE T1.COL1 = 'A' THEN
<COMPLEX CALCULATIONS>
END (This is given a name) A_AMOUNT
CASE T1.COL1 = 'B' THEN
<COMPLEX CALCULATIONS>
END (This is given a name) B_AMOUNT
CASE T1.COL1 = 'C' THEN
<COMPLEX CALCULATIONS>
END (This is given a name) C_AMOUNT
ELSE
CASE T1.COL1 = 'D' THEN
(Some Amount1 - This amount comes from some other calculation) - (A_AMOUNT-B_AMOUNT-C_AMOUNT)
END AMOUNT_ONE,
CASE T2.COL2 = 'A' THEN
<COMPLEX CALCULATIONS>
END (This is given a name) A_AMOUNT
CASE T3.COL2 = 'B' THEN
<COMPLEX CALCULATIONS>
END (This is given a name) B_AMOUNT
CASE T2.COL2 = 'C' THEN
<COMPLEX CALCULATIONS>
END (This is given a name) C_AMOUNT
ELSE
CASE T4.COL2 = 'D' THEN
(Some Amount2 - This amount comes from some other calculation) - (A_AMOUNT-B_AMOUNT-C_AMOUNT)
END AMOUNT_TWO
...
FROM TAB_ONE T1 LEFT JOIN TAB_TWO T2 ON(T1.COL1 = T2.COL1)
LEFT JOIN TAB_THREE T3 ON(T1.COL1 = T3.COL1)
LEFT JOIN TAB_FOUR T4 ON(T1.COL1 = T4.COL1)
First I need to display all the records from TAB_ONE which contains more than 10million records.
If you see there are columns like AMOUNT_ONE,AMOUNT_TWO which has got some complex calculations and that is based on some other calculations and which inturn and goes on.... Like this I have some ten amount columns. Finally these records has to be inserted into a new table.
To get this I have written nested inline queries to calculate these AMOUNT columns but since considering the huge amount of records, it takes more than 8 hours
for inserting this into a new table.
Please advise how do I restructure or tune this query or anyother better way ?
Thanks
Marlon
|
|
|
|
| Re: SQL Query Help [message #464613 is a reply to message #464608] |
Thu, 08 July 2010 08:52  |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Based on the information we have no way of knowing what's taking the time. It might be all the calculations but since you've hidden them from us we can't possibly suggest alternatives.
Read the sticky at the top of this forum and supply the required information - an explain plan would be a good start.
Also how long does this take:
SELECT *
FROM TAB_ONE T1 LEFT JOIN TAB_TWO T2 ON(T1.COL1 = T2.COL1)
LEFT JOIN TAB_THREE T3 ON(T1.COL1 = T3.COL1)
LEFT JOIN TAB_FOUR T4 ON(T1.COL1 = T4.COL1)
|
|
|
|
| Re: SQL Query Help [message #464619 is a reply to message #464613] |
Thu, 08 July 2010 09:14 |
marlon_loyolite
Messages: 66
Registered: July 2006
|
Member |
|
|
Friends,
Query is too big to post..
INSERT INTO NEW_TABLE
(ecar_id, acc_rk, cr_app_rk, snapshot_date,
multiple_run_indicator, cibc_rq_portfolio_cd,
product_qualifier, source_system,
basel_exposure_class_code, basel_exposure_type,
ccis_branch_of_account, ccis_org_unit,
ccis_product_org_id, ccis_account, ccis_product,
ccis_cust_group, ccis_ccy_code, ec_acc_securitized_ind,
investor_number, sub_investor_number, insurer_type_cd,
insurer_name, remaining_term_in_months, base_pd_percent,
ec_rc_pd_band, ec_pd_pool_id, ec_pd_value_percent,
ec_lgd_rate_percent, rc_pd_pool_id, rc_pd_value_percent,
rc_lgd_rate_percent, rc_r, ec_r,
pre_sec_authorized_cd_limit, pre_sec_outstanding_balance,
notional_principal_amt, pre_sec_ead_amt,
ec_post_sec_ead_amt, ec_post_sec_post_crm_ead_amt,
ec_sec_ead_amt, ec_crm_ead_amt, ec_capital_amt,
ec_annual_el_amt, rc_post_sec_ead_amt,
rc_post_sec_post_crm_ead_amt, rc_securitized_ead_amt,
rc_crm_ead_amt, rc_capital_amt, rc_annual_el_amt,
load_time, update_time, bcar_id, process_run_id)
SELECT /*+ APPEND */
0 ecar_id, acc_rk, cr_app_rk, snap_shot_date,
multiple_run_indicator, cibc_rq_portfolio_cd,
product_qualifier, source_system, basel_exposure_class_code,
basel_exposure_type, ccis_branch_of_account, ccis_org_unit,
ccis_product_org_id, ccis_account, ccis_product,
ccis_cust_group, ccis_ccy_code, ec_acc_securitized_ind,
investor_number, sub_investor_number, insurer_type_cd,
insurer_name, remaining_term_in_months, base_pd_percent,
ec_rc_pd_band, ec_pd_pool_id, ec_pd_value_percent,
ec_lgd_rate_percent, rc_pd_pool_id, rc_pd_value_percent,
rc_lgd_rate_percent, rc_r, ec_r, pre_sec_authorized_cd_limit,
pre_sec_outstanding_balance, notional_principal_amt,
pre_sec_ead_amt, ec_post_sec_ead_amt,
ec_post_sec_post_crm_ead_amt, ec_sec_ead_amt, ec_crm_ead_amt,
(CASE
WHEN case_chk = 1
THEN CASE
WHEN ccis_account <> 1301300 AND ead_amt != 0
THEN ( capital
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN ccis_account = 1301300
THEN (capital - NVL (ec_capital_case_1a, 0))
ELSE 0
END
WHEN case_chk = 2
THEN CASE
WHEN ccis_account <> 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( capital
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN ccis_account = 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( capital
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN basel_exposure_type = 'UNDRAW'
THEN ( capital
- NVL (ec_capital_case_2a, 0)
- NVL (ec_capital_case_2b, 0)
)
ELSE 0
END
WHEN case_chk = 3
THEN CASE
WHEN ccis_account <> 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( capital
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN ccis_account = 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( capital
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN basel_exposure_type = 'OOBS'
AND ead_amt != 0
THEN ( capital
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN basel_exposure_type = 'UNDRAW'
THEN ( capital
- NVL (ec_capital_case_3a, 0)
- NVL (ec_capital_case_3b, 0)
- NVL (ec_capital_case_3c, 0)
)
ELSE 0
END
END
) ec_capital_amt,
(CASE
WHEN case_chk = 1
THEN CASE
WHEN ccis_account <> 1301300 AND ead_amt != 0
THEN ( el_amt
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN ccis_account = 1301300
THEN (el_amt - NVL (ec_annual_case_1a, 0))
ELSE 0
END
WHEN case_chk = 2
THEN CASE
WHEN ccis_account <> 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( el_amt
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN ccis_account = 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( el_amt
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN basel_exposure_type = 'UNDRAW'
THEN ( el_amt
- NVL (ec_annual_case_2a, 0)
- NVL (ec_annual_case_2b, 0)
)
ELSE 0
END
WHEN case_chk = 3
THEN CASE
WHEN ccis_account <> 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( el_amt
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN ccis_account = 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( el_amt
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN basel_exposure_type = 'OOBS'
AND ead_amt != 0
THEN ( el_amt
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN basel_exposure_type = 'UNDRAW'
THEN ( el_amt
- NVL (ec_annual_case_3a, 0)
- NVL (ec_annual_case_3b, 0)
- NVL (ec_annual_case_3c, 0)
)
ELSE 0
END
END
) ec_annual_el_amt,
rc_post_sec_ead_amt, rc_post_sec_post_crm_ead_amt,
rc_securitized_ead_amt, rc_crm_ead_amt,
(CASE
WHEN basel_exposure_type IN ('DRAWN')
THEN CASE
WHEN ccis_account <> 1301300 AND ead_amt != 0
THEN ( post_sec_rwa_amt
* ( rc_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN ccis_account = 1301300
THEN ( post_sec_rwa_amt
- NVL (rc_capital_amt_a, 0)
)
ELSE 0
END
WHEN basel_exposure_type IN ('UNDRAW', 'OOBS')
THEN post_sec_rwa_amt
END
) rc_capital_amt,
(CASE
WHEN basel_exposure_type IN ('DRAWN')
THEN CASE
WHEN ccis_account <> 1301300 AND ead_amt != 0
THEN ( post_sec_el_amt
* ( rc_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN ccis_account = 1301300
THEN ( post_sec_el_amt
- NVL (rc_annual_el_amt_a, 0)
)
ELSE 0
END
WHEN basel_exposure_type IN ('UNDRAW', 'OOBS')
THEN post_sec_el_amt
END
) rc_annual_el_amt,
SYSDATE load_time, SYSDATE update_time, bcar_id,
NULL process_run_id
FROM (SELECT 0, acc_rk, cr_app_rk, snap_shot_date,
multiple_run_indicator, cibc_rq_portfolio_cd,
product_qualifier, source_system,
basel_exposure_class_code, basel_exposure_type,
ccis_branch_of_account, ccis_org_unit,
ccis_product_org_id, ccis_account, ccis_product,
ccis_cust_group, ccis_ccy_code,
ec_acc_securitized_ind, investor_number,
sub_investor_number, insurer_type_cd, insurer_name,
remaining_term_in_months, base_pd_percent,
ec_rc_pd_band, ec_pd_pool_id, ec_pd_value_percent,
ec_lgd_rate_percent, rc_pd_pool_id,
rc_pd_value_percent, rc_lgd_rate_percent, rc_r, ec_r,
pre_sec_authorized_cd_limit,
pre_sec_outstanding_balance, notional_principal_amt,
pre_sec_ead_amt, post_sec_rwa_amt, post_sec_el_amt,
ead_amt, capital, el_amt, ec_post_sec_ead_amt,
ec_post_sec_post_crm_ead_amt,
(pre_sec_ead_amt - ec_post_sec_ead_amt
) ec_sec_ead_amt,
(ec_post_sec_ead_amt - ec_post_sec_post_crm_ead_amt
) ec_crm_ead_amt,
(CASE
WHEN case_chk = 1
THEN CASE
WHEN ccis_account <> 1301300
AND ead_amt != 0
THEN ( capital
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN ccis_account = 1301300
THEN 0
END
END
) ec_capital_case_1a,
(CASE
WHEN case_chk = 2
THEN CASE
WHEN ccis_account <> 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( capital
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
END
END
) ec_capital_case_2a,
(CASE
WHEN case_chk = 2
THEN CASE
WHEN ccis_account = 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( capital
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN basel_exposure_type = 'UNDRAW'
THEN 0
END
END
) ec_capital_case_2b,
(CASE
WHEN case_chk = 3
THEN CASE
WHEN ccis_account <> 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( capital
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
END
END
) ec_capital_case_3a,
(CASE
WHEN case_chk = 3
THEN CASE
WHEN ccis_account = 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( capital
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
END
END
) ec_capital_case_3b,
(CASE
WHEN case_chk = 3
THEN CASE
WHEN basel_exposure_type = 'OOBS'
AND ead_amt != 0
THEN ( capital
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN basel_exposure_type = 'UNDRAW'
THEN 0
END
END
) ec_capital_case_3c,
(CASE
WHEN case_chk = 1
THEN CASE
WHEN ccis_account <> 1301300
AND ead_amt != 0
THEN ( el_amt
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN ccis_account = 1301300
THEN 0
END
END
) ec_annual_case_1a,
(CASE
WHEN case_chk = 2
THEN CASE
WHEN ccis_account <> 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( el_amt
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
END
END
) ec_annual_case_2a,
(CASE
WHEN case_chk = 2
THEN CASE
WHEN ccis_account = 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( el_amt
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN basel_exposure_type = 'UNDRAW'
THEN 0
END
END
) ec_annual_case_2b,
(CASE
WHEN case_chk = 3
THEN CASE
WHEN ccis_account <> 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( el_amt
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
END
END
) ec_annual_case_3a,
(CASE
WHEN case_chk = 3
THEN CASE
WHEN ccis_account = 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( el_amt
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
END
END
) ec_annual_case_3b,
(CASE
WHEN case_chk = 3
THEN CASE
WHEN basel_exposure_type = 'OOBS'
AND ead_amt != 0
THEN ( el_amt
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN basel_exposure_type = 'UNDRAW'
THEN 0
END
END
) ec_annual_case_3c,
rc_post_sec_ead_amt, rc_post_sec_post_crm_ead_amt,
(pre_sec_ead_amt - rc_post_sec_ead_amt
) rc_securitized_ead_amt,
(rc_post_sec_ead_amt - rc_post_sec_post_crm_ead_amt
) rc_crm_ead_amt,
case_chk,
(CASE
WHEN basel_exposure_type IN ('DRAWN')
THEN CASE
WHEN ccis_account <> 1301300
AND ead_amt != 0
THEN ( post_sec_rwa_amt
* ( rc_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN ccis_account = 1301300
THEN 0
END
WHEN basel_exposure_type IN ('UNDRAW', 'OOBS')
THEN post_sec_rwa_amt
END
) rc_capital_amt_a,
(CASE
WHEN basel_exposure_type IN ('DRAWN')
THEN CASE
WHEN ccis_account <> 1301300
AND ead_amt != 0
THEN ( post_sec_el_amt
* ( rc_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN ccis_account = 1301300
THEN 0
END
WHEN basel_exposure_type IN ('UNDRAW', 'OOBS')
THEN post_sec_el_amt
END
) rc_annual_el_amt_a,
bcar_id
FROM (SELECT acc_rk, cr_app_rk, snap_shot_date,
multiple_run_indicator, cibc_rq_portfolio_cd,
product_qualifier, source_system,
basel_exposure_class_code,
basel_exposure_type, ccis_branch_of_account,
ccis_org_unit, ccis_product_org_id,
ccis_account, ccis_product, ccis_cust_group,
ccis_ccy_code, ec_acc_securitized_ind,
investor_number, sub_investor_number,
insurer_type_cd, insurer_name,
remaining_term_in_months, base_pd_percent,
ec_rc_pd_band, ec_pd_pool_id,
ec_pd_value_percent, ec_lgd_rate_percent,
rc_pd_pool_id, rc_pd_value_percent,
rc_lgd_rate_percent, rc_r, ec_r, bcar_id,
(CASE
WHEN acc_rk = -1
THEN pre_sec_ead_amt
WHEN basel_exposure_type = 'DRAWN'
THEN GREATEST
(pre_sec_outstanding_balance,
0
)
WHEN basel_exposure_type = 'OOBS'
THEN pre_sec_ead_amt
ELSE ( pre_sec_authorized_cd_limit
- GREATEST
(pre_sec_outstanding_balance,
0
)
- pre_sec_ead_amt
)
END
) pre_sec_authorized_cd_limit,
pre_sec_outstanding_balance
pre_sec_outstanding_balance,
notional_principal_amt notional_principal_amt,
pre_sec_ead_amt pre_sec_ead_amt,
post_sec_rwa_amt, post_sec_el_amt, ead_amt,
capital, el_amt,
(CASE
WHEN acc_securitized_ind = 'E'
THEN 0
WHEN acc_securitized_ind = 'Y'
THEN post_sec_ead_amt
ELSE pre_sec_ead_amt
END
) rc_post_sec_ead_amt,
(CASE
WHEN ( investor_number = 2000
/*TBD*/
OR source_system = 'TSYS'
)
THEN pre_sec_ead_amt
WHEN ( acc_securitized_ind = 'E'
AND (investor_number = 2000)
)
THEN 0
ELSE CASE
WHEN acc_securitized_ind = 'E'
THEN 0
WHEN acc_securitized_ind = 'Y'
THEN post_sec_ead_amt
ELSE pre_sec_ead_amt
END
END
) ec_post_sec_ead_amt,
(CASE
WHEN ccis_product_group = 'Mortgages'
AND acc_securitized_ind = 'E'
THEN 0
WHEN ccis_product_group = 'Mortgages'
AND insurer_type_cd IN ('GVN', 'PRI')
THEN 0
WHEN ccis_product_group = 'Student_Loans'
AND source_system IN ('LAS')
THEN 0
WHEN ccis_product_group = 'Student_Loans'
AND source_system IN ('EDULINX')
THEN post_gov_ead_amt
ELSE CASE
WHEN acc_securitized_ind = 'E'
THEN 0
WHEN acc_securitized_ind = 'Y'
THEN post_sec_ead_amt
ELSE pre_sec_ead_amt
END
END
) rc_post_sec_post_crm_ead_amt,
(CASE
WHEN ccis_product_group = 'Mortgages'
AND acc_securitized_ind = 'E'
AND (investor_number = 2000)
THEN 0
WHEN ccis_product_group = 'Mortgages'
AND insurer_type_cd IN ('GVN', 'PRI')
THEN 0
WHEN ccis_product_group = 'Student_Loans'
AND source_system IN ('LAS')
THEN 0
WHEN ccis_product_group = 'Student_Loans'
AND source_system IN ('EDULINX')
THEN post_gov_ead_amt
WHEN investor_number = 2000
AND insurer_type_cd IN ('GVN', 'PRI')
THEN 0
ELSE CASE
WHEN ( investor_number = 2000
OR source_system = 'TSYS'
)
THEN pre_sec_ead_amt
WHEN ( acc_securitized_ind = 'E'
AND (investor_number = 2000)
)
THEN 0
ELSE CASE
WHEN acc_securitized_ind = 'E'
THEN 0
WHEN acc_securitized_ind = 'Y'
THEN post_sec_ead_amt
ELSE pre_sec_ead_amt
END
END
END
) ec_post_sec_post_crm_ead_amt,
COUNT
(DISTINCT basel_exposure_type) OVER (PARTITION BY acc_rk, cr_app_rk)
AS case_chk
FROM (SELECT sbd.acc_rk acc_rk,
sbd.cr_app_rk cr_app_rk,
sbd.month_end_date snap_shot_date,
NULL multiple_run_indicator,
(CASE
WHEN sbd.source_system IN ('ML')
THEN 'Margin_Lending'
WHEN sbd.source_system IN
('ICBS')
THEN 'Amicus'
|| sbd.ccis_product_group
WHEN sbd.source_system IN
('CLSORG')
THEN 'CBNF_Excalibur'
WHEN sbd.source_system IN
('EXCALIBUR')
AND sbd.exposure_code = 'UNDRAW'
AND sbd.acc_rk = -1
THEN 'CBNF_Excalibur'
WHEN sbd.source_system IN
('TARGET')
AND sbd.exposure_code = 'UNDRAW'
AND sbd.acc_rk = -1
THEN 'CBNF_Target'
WHEN sbd.source_system IN
('TARGET')
AND sbd.exposure_class_code =
'OTHRET'
THEN 'Mortgages'
WHEN sbd.source_system IN ('PCL')
AND sifp.subproduct_cd = '009'
THEN 'PLC_Unsecured'
WHEN sbd.source_system IN ('PCL')
AND sifp.subproduct_cd = '004'
THEN 'PLC_Secured_Other'
WHEN sbd.source_system IN ('PCL')
AND sifp.subproduct_cd = '001'
THEN 'PLC_Secured_RE'
WHEN sbd.source_system IN
('CLASS')
AND sifp.subproduct_cd = '009'
THEN 'PL_Unsecured'
WHEN sbd.source_system IN
('CLASS')
AND sifp.subproduct_cd = '001'
THEN 'PL_Secured_RE'
WHEN sbd.source_system IN
('CLASS')
AND sifp.subproduct_cd = '004'
THEN 'PL_Secured_Other'
WHEN sbd.source_system IN
('PCASAV')
THEN 'Overdrafts'
ELSE sbd.ccis_product_group
END
) cibc_rq_portfolio_cd,
(CASE
WHEN sbd.acc_rk = -1
THEN 'Committed not Funded'
WHEN sbd.exposure_class_code IN
('REVOLV', 'HELOC')
THEN 'Revolving'
WHEN sbd.exposure_class_code =
'OTHRET'
AND sbd.source_system = 'PCL'
THEN 'Revolving'
ELSE 'Term'
END
) product_qualifier,
sbd.source_system source_system,
sbd.exposure_class_code
basel_exposure_class_code,
sbd.exposure_code basel_exposure_type,
sbd.ccis_branch_of_account
ccis_branch_of_account,
sbd.ccis_org_unit ccis_org_unit,
sbd.ccis_product_group
ccis_product_group,
sbd.ccis_product_org_id
ccis_product_org_id,
sbd.ccis_account ccis_account,
sbd.ccis_product ccis_product,
sbd.ccis_cust_group ccis_cust_group,
sbd.ccis_ccy_code ccis_ccy_code,
sbd.post_gov_ead_amt,
sbd.post_sec_ead_amt,
sbd.post_sec_rwa_amt,
sbd.post_sec_el_amt, sive.ead_amt,
sive.capital, sive.el_amt,
sbd.acc_securitized_ind,
(CASE
WHEN sbd.ccis_product_group =
'Cards'
THEN 'Non-securitised'
WHEN sbd.ccis_product_group =
'Mortgages'
AND sfarp.mtge_investor_id = 2000
THEN 'Non-securitised'
WHEN sbd.acc_securitized_ind = 'E'
THEN 'Fully Securitised'
ELSE 'Non-securitised'
END
) ec_acc_securitized_ind,
sfarp.mtge_investor_id
investor_number,
(SELECT smai.securitized_deal_id
FROM rcdwstg.stg_mrtg_acc_investor smai
WHERE smai.acc_rk = sbd.acc_rk
AND smai.source_system = sbd.source_system)
sub_investor_number,
(CASE
WHEN sbd.ccis_product_group =
'Student_Loans'
AND sbd.post_sec_ead_amt <>
sbd.post_gov_ead_amt
THEN 'GVN'
ELSE sbd.insurer_type_cd
END
) insurer_type_cd,
(SELECT DISTINCT insurer_name
FROM rcdwstg.stg_fin_acc_insurance sfai
WHERE sfai.acc_rk =
sbd.acc_rk
AND sfai.source_system =
sbd.source_system)
insurer_name,
(CASE
WHEN sbd.exposure_class_code IN
('HELOC', 'REVOLV')
THEN 1
WHEN sbd.ccis_product_group IN
('Overdraft',
'PLC_Secured_Other',
'PLC_Secured_RE',
'PLC_Unsecured'
)
THEN 1
WHEN ( sfarp.term_maturity_dt
- sbd.month_end_date
) < 0
THEN 1
WHEN ( sbd.ccis_product_group =
'Mortgages'
AND (CASE
WHEN sbd.acc_rk = -1
THEN 'Committed not Funded'
WHEN sbd.exposure_class_code IN
('REVOLV',
'HELOC'
)
THEN 'Revolving'
WHEN sbd.exposure_class_code =
'OTHRET'
AND sbd.source_system =
'PCL'
THEN 'Revolving'
ELSE 'Term'
END
) =
'Committed not Funded'
)
THEN 2
WHEN ( sbd.source_system =
'EDULINX'
AND sbd.ccis_product_group =
'Student_Loans'
)
THEN TO_DATE ('31-JAN-2013')
- sbd.month_end_date
ELSE sfarp.term_maturity_dt
- sbd.month_end_date
END
) remaining_term_in_months,
(SELECT DISTINCT mean_estimate
FROM REFERENCE.ref_pd_pool_characters rppc
WHERE rppc.assessment_rating_grade =
sbd.pd_model_id)
base_pd_percent,
sbd.pd_band ec_rc_pd_band,
sive.pd_model_rating_grade
ec_pd_pool_id,
sive.pd_val ec_pd_value_percent,
sive.lgd_val ec_lgd_rate_percent,
sbd.pd_model_id rc_pd_pool_id,
sbd.pd_value rc_pd_value_percent,
sbd.lgd_rate rc_lgd_rate_percent,
(CASE
WHEN sbd.exposure_class_code =
'OTHERET'
THEN 0.03
* ( 1
- EXP
( -35
* sive.pd_val
/ 100
)
/ (1 - EXP (-35))
)
+ 0.16
* ( 1
- ( 1
- EXP
( -35
* sive.pd_val
/ 100
)
/ (1 - EXP (-35)
)
)
)
ELSE (CASE
WHEN ( sbd.exposure_class_code =
'REVOLV'
AND sbd.ccis_product_group =
'Cards'
)
THEN 0.012
WHEN ( sbd.exposure_class_code =
'REVOLV'
AND sbd.ccis_product_group !=
'Cards'
)
THEN 0.04
WHEN (sbd.exposure_class_code IN
('REMORT',
'HELOC'
)
)
THEN 0.08
WHEN ( sbd.exposure_class_code =
'OTHERET'
AND sbd.ccis_product_group =
'Student_Loans'
)
THEN 0.04
WHEN ( sbd.exposure_class_code =
'OTHERET'
AND sbd.ccis_product_group !=
'Student_Loans'
)
THEN 0.08
ELSE NULL
END
)
END
) rc_r /*TBD*/,
(CASE
WHEN sbd.exposure_class_code =
'REVOLV'
THEN 0.04
WHEN sbd.exposure_class_code IN
('REMORT', 'HELOC')
THEN 0.15
ELSE NULL
END
) ec_r,
sbd.pre_sec_authorized_cd_limit
pre_sec_authorized_cd_limit,
NVL
(sbd.pre_sec_outstanding_balance,
0
) pre_sec_outstanding_balance,
(CASE
WHEN sive.pd_val = 100
THEN 0
ELSE sbd.notional_principal_amt
END
) notional_principal_amt,
(CASE
WHEN sbd.exposure_code = 'DRAWN'
THEN GREATEST
(NVL
(sbd.pre_sec_outstanding_balance,
0
),
0
)
WHEN sbd.exposure_code = 'OOBS'
THEN sbd.pre_sec_ead_amt
ELSE ( NVL (sive.ead_amt, 0)
- GREATEST
(NVL
(sbd.pre_sec_outstanding_balance,
0
),
0
)
- NVL (sbd.pre_sec_ead_amt,
0
)
)
END
) pre_sec_ead_amt,
sbd.bcar_id
FROM TMP_TABLE_ONE sbd LEFT JOIN TMP_TABLE_TWO sive
ON ( sbd.acc_rk = sive.acc_rk
AND sbd.cr_app_rk = sive.cr_app_rk
AND sbd.source_system = sive.source_system
)
LEFT JOIN TMP_TABLE_THREE sifp
ON (sbd.acc_rk = sifp.acc_rk
AND sbd.source_system = sifp.source_system
)
LEFT JOIN TMP_TABLE_FOUR sfarp
ON ( sbd.acc_rk = sfarp.acc_rk
AND sbd.cr_app_rk = sfarp.cr_app_rk
AND sbd.source_system = sfarp.source_system
)
)));
|
|
|
|
| Re: SQL Query Help [message #464621 is a reply to message #464619] |
Thu, 08 July 2010 09:15 |
marlon_loyolite
Messages: 66
Registered: July 2006
|
Member |
|
|
Its Explain plan output.
Plan hash value: 1466651471
-----------------------------------------------------------------------------------------------------------------------------------------------------
Id |Operation |Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ D
-----------------------------------------------------------------------------------------------------------------------------------------------------
0|INSERT STATEMENT | | 11M| 5815M| | 23474 (3)| 00:04:42 | | | | |
1| LOAD AS SELECT |NEW_TABLE | | | | | | | | | |
2| VIEW | | 11M| 5815M| | 23474 (3)| 00:04:42 | | | | |
3| PX COORDINATOR | | | | | | | | | | |
4| PX SEND QC (RANDOM) |:TQ10007 | 11M| 3067M| | 23474 (3)| 00:04:42 | | | Q1,07 | P->S | QC (
5| WINDOW SORT | | 11M| 3067M| 6417M| 23474 (3)| 00:04:42 | | | Q1,07 | PCWP |
6| PX RECEIVE | | 11M| 3067M| | 7117 (7)| 00:01:26 | | | Q1,07 | PCWP |
7| PX SEND HASH |:TQ10006 | 11M| 3067M| | 7117 (7)| 00:01:26 | | | Q1,06 | P->P | HASH
* 8| HASH JOIN RIGHT OUTER BUFFERED | | 11M| 3067M| | 7117 (7)| 00:01:26 | | | Q1,06 | PCWP |
9| PX RECEIVE | | 4514K| 68M| | 118 (14)| 00:00:02 | | | Q1,06 | PCWP |
10| PX SEND HASH |:TQ10004 | 4514K| 68M| | 118 (14)| 00:00:02 | | | Q1,04 | P->P | HASH
11| PX BLOCK ITERATOR | | 4514K| 68M| | 118 (14)| 00:00:02 | 1 | 6 | Q1,04 | PCWC |
12| TABLE ACCESS FULL |TMP_TABLE_THREE | 4514K| 68M| | 118 (14)| 00:00:02 | 1 | 6 | Q1,04 | PCWP |
13| PX RECEIVE | | 11M| 2885M| | 6982 (7)| 00:01:24 | | | Q1,06 | PCWP |
14| PX SEND HASH |:TQ10005 | 11M| 2885M| | 6982 (7)| 00:01:24 | | | Q1,05 | P->P | HASH
* 15| HASH JOIN RIGHT OUTER BUFFERED| | 11M| 2885M| 30M| 6982 (7)| 00:01:24 | | | Q1,05 | PCWP |
16| PX RECEIVE | | 6684K| 408M| | 470 (10)| 00:00:06 | | | Q1,05 | PCWP |
17| PX SEND HASH |:TQ10002 | 6684K| 408M| | 470 (10)| 00:00:06 | | | Q1,02 | P->P | HASH
18| PX BLOCK ITERATOR | | 6684K| 408M| | 470 (10)| 00:00:06 | 1 | 15 | Q1,02 | PCWC |
19| TABLE ACCESS FULL |TMP_TABLE_TWO | 6684K| 408M| | 470 (10)| 00:00:06 | 1 | 15 | Q1,02 | PCWP |
20| PX RECEIVE | | 11M| 2158M| | 4102 (10)| 00:00:50 | | | Q1,05 | PCWP |
21| PX SEND HASH |:TQ10003 | 11M| 2158M| | 4102 (10)| 00:00:50 | | | Q1,03 | P->P | HASH
* 22| HASH JOIN BUFFERED | | 11M| 2158M| 38M| 4102 (10)| 00:00:50 | | | Q1,03 | PCWP |
23| PX RECEIVE | | 21M| 370M| | 964 (21)| 00:00:12 | | | Q1,03 | PCWP |
24| PX SEND HASH |:TQ10000 | 21M| 370M| | 964 (21)| 00:00:12 | | | Q1,00 | P->P | HASH
25| PX BLOCK ITERATOR | | 21M| 370M| | 964 (21)| 00:00:12 | 1 | 13 | Q1,00 | PCWC |
26| TABLE ACCESS FULL |TMP_TABLE_FOUR | 21M| 370M| | 964 (21)| 00:00:12 | 1 | 13 | Q1,00 | PCWP |
27| PX RECEIVE | | 11M| 1953M| | 771 (14)| 00:00:10 | | | Q1,03 | PCWP |
28| PX SEND HASH |:TQ10001 | 11M| 1953M| | 771 (14)| 00:00:10 | | | Q1,01 | P->P | HASH
29| PX BLOCK ITERATOR | | 11M| 1953M| | 771 (14)| 00:00:10 | 1 | 17 | Q1,01 | PCWC |
30| TABLE ACCESS FULL |TMP_TABLE_ONE | 11M| 1953M| | 771 (14)| 00:00:10 | 1 | 17 | Q1,01 | PCWP |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("SBD"."SOURCE_SYSTEM"="SIFP"."SOURCE_SYSTEM"(+) AND "SBD"."ACC_RK"="SIFP"."ACC_RK"(+))
15 - access("SBD"."SOURCE_SYSTEM"="SIVE"."SOURCE_SYSTEM"(+) AND "SBD"."CR_APP_RK"="SIVE"."CR_APP_RK"(+) AND "SBD"."ACC_RK"="SIVE"."ACC_RK"(+))
22 - access("SBD"."SOURCE_SYSTEM"="SFARP"."SOURCE_SYSTEM" AND "SBD"."ACC_RK"="SFARP"."ACC_RK" AND "SBD"."CR_APP_RK"="SFARP"."CR_APP_RK")*/
Quote:Edited by Kevin Meade for better readability on the screen. I removed as much white space was reasonable and then cut off the last few characters so it fit on the screen (at least on my screen anyway).
[Updated on: Fri, 09 July 2010 02:06] by Moderator Report message to a moderator |
|
|
|
| Re: SQL Query Help [message #464623 is a reply to message #464621] |
Thu, 08 July 2010 09:17 |
marlon_loyolite
Messages: 66
Registered: July 2006
|
Member |
|
|
Some more detail..
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.3
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
SQL> show parameter db_file_multi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 32
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 32768
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> column sname format a20
SQL> column pname format a20
SQL> column pval2 format a20
SQL> select sname, pname, pval1, pval2 from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 04-25-2010 14:50
SYSSTATS_INFO DSTOP 04-25-2010 14:50
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 587.851
SYSSTATS_MAIN IOSEEKTIM 11.022
SYSSTATS_MAIN IOTFRSPEED 33524.073
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
FYI..The TABLE_ONE is partitioned and NEW_TABLE is also partitioned.
Thanks
Marlon
|
|
|
|
|
|
| Re: SQL Query Help [message #464672 is a reply to message #464626] |
Thu, 08 July 2010 12:35 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That's the biggest select statement I've ever seen!
I know we generally say do everything in a single SQL where possible but that's taking it a bit far don't you think?
I'd be breaking that up to into chunks simply because I think anything that big unmaintainable, regardless of performance.
You appear to have some cases that are redundant:
THEN CASE
WHEN ccis_account <> 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( capital
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
WHEN ccis_account = 1301300
AND ead_amt != 0
AND basel_exposure_type = 'DRAWN'
THEN ( capital
* ( ec_post_sec_post_crm_ead_amt
/ ead_amt
)
)
They do exactly the same thing so there is no point doing both.
Check to see if there are any others and get rid of them.
Past that I would probably suggest breaking it up into at least 2 quereies - one that gets the rows where ccis_account = 1301300 and the other where ccis_account != 1301300.
That might not speed it up but it'll certainly make it a lot easier to follow.
It also looks like there some problems with the data model. For example the CASE statement for cibc_rq_portfolio_cd - there should be a column in a table somewhere that tells you that - you shouldn't be coding a case for it.
Have you checked how long my query takes?
|
|
|
|
| Re: SQL Query Help [message #464731 is a reply to message #464608] |
Fri, 09 July 2010 01:44 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
When dealing with big ideas, it pays to employ abstraction. By abstraction in this case I mean the ability to ignore details. To tune this sql statement, we will need to work it one piece at a time, by removing details and then putting them back in as we get a handle on the various pieces of what we have. As the OP has hinted to, often times when scalar subqueries are added to a query, query performance can go out the window. So one of the steps in trying to deal with the performance of such a query is to figure out which of the scalar subqueries is causing the hit, then tune it. Another alternative are the forms of subquery factoring (WITH CLAUSE being one such alternative).
So OP, you need to do some basic detective work to find your performance issue (if indeed there is one).
1) comment out all scalar subqueries and get a timing for the main query.
2) add each scalar subquery back in one at a time or in batches as you can handle and figure out which one(s) are causing your slowdown. Might be more than one.
3) tune the problems.
Here is an example. Your first look at this query:
select
null c1
, null c2
, (select null from dual) c3
, (select null from dual) c4
, (
select ...
from ... <some big tables>
where ... <some joins to big tables>
) c5
, (select null from dual) c6
from dual
/
Might be something like this:
select
null c1
, null c2
from dual
/
select
null c1
, null c2
, (select null from dual) c3
from dual
/
select
null c1
, null c2
, (select null from dual) c3
, (select null from dual) c4
from dual
/
select
null c1
, null c2
, (select null from dual) c3
, (select null from dual) c4
, (
select ...
from ... <some big tables>
where ... <some joins to big tables>
) c5
from dual
/
select
null c1
, null c2
, (select null from dual) c3
, (select null from dual) c4
, (
select ...
from ... <some big tables>
where ... <some joins to big tables>
) c5
, (select null from dual) c6
from dual
/
For this example, my guess is C5 needs tuning (hehe).
You get the idea right? Using this approach, you can figure out how much resources and wall time are being taken by each major component of the query, then you can target your tuning efforts. You may also find that the scalar subqueries are not your issue and it is the main query who knows. But using this approach you will find out.
If you think this is a lot of work, you are correct. If you are a lazy bumm and don't want to work hard at your IT job, that is good because God gave every good IT guy/gal a lazy gene to motivate him/her to produce better smarter solutions. But sometimes, work is required, so if you are not willing to work hard when needed, then get out of the business because you won't like being an IT professional. This query and its tuning needs are a great example of the every day reality of IT; thanks for providing it for us to look at.
Do this work and post back what you find please.
Good luck, Kevin
[Updated on: Fri, 09 July 2010 02:24] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sat May 04 01:57:15 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
