Results 1 to 4 of 4

Thread: SQL Performance

  1. #1
    Join Date
    Apr 2011
    Posts
    4

    Unanswered: SQL Performance

    Hello,
    Need help on query performance, for below query below is explain plan

    I have created idnex on pe_val_ch table for (iva_investment_id, valuation_date_id, investemnt_type) still it shows the full table scan for pe_val_ch

    update pe_val_ch pc set ( pc.local_gp_val_amt, pc.base_gp_val_amt,pc.gp_val_status) = ( select local.amount, base.amount, local.approve_cap_bal_cltspec
    from pedge_staging_val local , pedge_staging_val base , pe_investments pi,pe_funds f
    where
    pi.iva_investment_id = local.iva_investment_id
    and local.iva_investment_id = base.iva_investment_id
    and base.period_end_date =local.period_end_date
    and local.iva_investment_id = pc.iva_investment_id
    and local.period_end_date = pc.valuation_date_id
    and pi.iva_fund_id = f.iva_fund_id
    and local.fin_currency = pi.curr_code
    and base.fin_currency = f.base_curr_code
    and pc.investment_type ='PI'

    below is exlain for above query

    Execution Plan
    ----------------------------------------------------------
    0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=1842 Card=221471 Bytes=13731202)
    1 0 UPDATE OF 'PE_VAL_CH'
    2 1 TABLE ACCESS (FULL) OF 'PE_VAL_CH' (TABLE) (Cost=1842 Card=221471 Bytes=13731202)
    3 1 FILTER
    4 3 TABLE ACCESS (BY INDEX ROWID) OF 'PEDGE_STAGING_VAL' (TABLE) (Cost=2 Card=1 Bytes=25)
    5 4 NESTED LOOPS (Cost=7 Card=1 Bytes=73)
    6 5 NESTED LOOPS (Cost=5 Card=1 Bytes=48)
    7 6 NESTED LOOPS (Cost=3 Card=1 Bytes=21)
    8 7 TABLE ACCESS (BY INDEX ROWID) OF 'PE_INVESTMENTS' (TABLE) (Cost=2 Card=1 Bytes=14)
    9 8 INDEX (UNIQUE SCAN) OF 'PE_INVESTMENTS_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
    10 7 TABLE ACCESS (BY INDEX ROWID) OF 'PE_FUNDS' (TABLE) (Cost=1 Card=1620 Bytes=11340)
    11 10 INDEX (UNIQUE SCAN) OF 'PE_FUNDS_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
    12 6 TABLE ACCESS (BY INDEX ROWID) OF 'PEDGE_STAGING_VAL' (TABLE) (Cost=2 Card=1 Bytes=27)
    13 12 INDEX (RANGE SCAN) OF 'PEDGE_STAGING_VAL_IDX_01' (INDEX) (Cost=1 Card=1)
    14 5 INDEX (RANGE SCAN) OF 'PEDGE_STAGING_VAL_IDX_01' (INDEX) (Cost=1 Card=1)

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    still it shows the full table scan for pe_val_ch
    That is because you have not set any restricting conditions for the table being "UPDATE"ed.

    Try this:
    Code:
    UPDATE pe_val_ch pc
       SET (pc.local_gp_val_amt, pc.base_gp_val_amt, pc.gp_val_status) =
              (SELECT local.amount, base.amount, local.approve_cap_bal_cltspec
                 FROM pedge_staging_val local,
                      pedge_staging_val base,
                      pe_investments pi,
                      pe_funds f
                WHERE     pi.iva_investment_id = local.iva_investment_id
                      AND local.iva_investment_id = base.iva_investment_id
                      AND base.period_end_date = local.period_end_date
                      AND local.iva_investment_id = pc.iva_investment_id
                      AND local.period_end_date = pc.valuation_date_id
                      AND pi.iva_fund_id = f.iva_fund_id
                      AND local.fin_currency = pi.curr_code
                      AND base.fin_currency = f.base_curr_code
                      AND pc.investment_type = 'PI')
     WHERE EXISTS
              (SELECT '?'
                 FROM pedge_staging_val local,
                      pedge_staging_val base,
                      pe_investments pi,
                      pe_funds f
                WHERE     pi.iva_investment_id = local.iva_investment_id
                      AND local.iva_investment_id = base.iva_investment_id
                      AND base.period_end_date = local.period_end_date
                      AND local.iva_investment_id = pc.iva_investment_id
                      AND local.period_end_date = pc.valuation_date_id
                      AND pi.iva_fund_id = f.iva_fund_id
                      AND local.fin_currency = pi.curr_code
                      AND base.fin_currency = f.base_curr_code
                      AND pc.investment_type = 'PI');

    PS: Next time use the "code" tags to format your query.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2011
    Posts
    4
    thanks for correcting

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    NP, It happens to all pf us...
    Good luck!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •