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
