Same SQL shows different execution path??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Same SQL shows different execution path??

  1. #1
    Join Date
    Oct 2003
    Posts
    65

    Same SQL shows different execution path??

    Dude,

    I have a SQL where in the execution path differs.can someone please explain why is this difference.

    SQL(with accounting_period BETWEEN 310 AND 364)
    ================================================
    select business_unit, ledger, account, product, deptid, operating_unit,
    scb_custcls, project_id, affiliate, currency_cd, base_currency,
    SUM(posted_total_amt), SUM(posted_base_amt),
    SUM(posted_tran_amt)
    from ps_ledger_adb
    where business_unit='156'
    and ledger='ACTUAL_LCY'
    and fiscal_year=2003
    and accounting_period BETWEEN 310 AND 364
    and deptid='1425'
    and account='111101'
    group by business_unit, ledger, account, product, deptid, operating_unit,
    scb_custcls, project_id, affiliate, currency_cd, base_currency

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=1 Bytes=73)
    1 0 SORT (GROUP BY) (Cost=50 Card=1 Bytes=73)
    2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PS_LEDGER_ADB' (Cost=4
    6 Card=1 Bytes=73)

    3 2 INDEX (SKIP SCAN) OF 'PS_LEDGER_ADB' (UNIQUE) (Cost=17
    9 Card=1)

    SQL(with accounting_period BETWEEN 320 AND 364)
    ================================================

    1 select business_unit, ledger, account, product, deptid, operating_unit,
    2 scb_custcls, project_id, affiliate, currency_cd, base_currency,
    3 SUM(posted_total_amt), SUM(posted_base_amt),
    4 SUM(posted_tran_amt)
    5 from sysadm.ps_ledger_adb
    6 where business_unit='156'
    7 and ledger='ACTUAL_LCY'
    8 and fiscal_year=2003
    9 and accounting_period BETWEEN 320 AND 364
    10 and deptid='1425'
    11 and account='111101'
    12 group by business_unit, ledger, account, product, deptid, operating_unit,
    13* scb_custcls, project_id, affiliate, currency_cd, base_currency
    SQL> /

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=1 Bytes=73)
    1 0 SORT (GROUP BY) (Cost=43 Card=1 Bytes=73)
    2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PS_LEDGER_ADB' (Cost=3
    9 Card=1 Bytes=73)

    3 2 INDEX (RANGE SCAN) OF 'PS_LEDGER_ADB' (UNIQUE) (Cost=1
    54 Card=17)

    From the above 1 goes for index skip scan and the other uses RANGE SCAN why?

    and accounting_period BETWEEN 310 AND 364 9 and accounting_period BETWEEN 320 AND 364

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    well that is not same SQL to start with, different predicates

  3. #3
    Join Date
    Oct 2002
    Posts
    807
    Plans can change even if you use bind variables.

Posting Permissions

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



Click Here to Expand Forum to Full Width