Re: Index Range Scan vs Fast full scan

  • From: "Ken Naim" <kennaim@xxxxxxxxx>
  • To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
  • Date: Wed, 10 Jan 2007 17:49:22 -0500

the sql isn't too complex just a 3 table join. Table a is 225M rows
while table b and c are less than 2k each. I didn't write the query,
jsut rying to tune it. Yes i am using the index_ffs hint.

select /*+ index_ffs(a uabopen_balance_id_index) */ uabopen_cust_code,
      uabopen_prem_code,
      utracct_account_a,
      uabopen_bad_debt_status_code  bd_status_code,
      sum(uabopen_bd_balance) bd_balance,
      sum(decode(uabopen_printed_ind,'n',uabopen_balance,0)) unbilled_chg,
      sum(uabopen_balance) open_balance,
      sum(uabopen_budget_variance) variance,
      sum(decode(uabopen_balance_ind,'n',uabopen_balance,0)) credit_balance,
      
sum(decode(uabopen_balance_ind,'n',0,decode(least((trunc(to_date(:g_bussiness_post_date))
- 
(-99999)),trunc(uabopen_due_date)),greatest((trunc(to_date(:g_bussiness_post_date))),trunc(uabopen_due_date)),uabopen_balance,0)))
bal_current,
      
sum(decode(uabopen_balance_ind,'n',0,decode(least((trunc(to_date(:g_bussiness_post_date))
- 1),trunc(uabopen_due_date)),greatest((trunc(to_date(:g_bussiness_post_date))
- 30),trunc(uabopen_due_date)),uabopen_balance,0))) bal_1_30,
      
sum(decode(uabopen_balance_ind,'n',0,decode(least((trunc(to_date(:g_bussiness_post_date))
- 31),trunc(uabopen_due_date)),greatest((trunc(to_date(:g_bussiness_post_date))
- 60),trunc(uabopen_due_date)),uabopen_balance,0))) bal_31_60,
      
sum(decode(uabopen_balance_ind,'n',0,decode(least((trunc(to_date(:g_bussiness_post_date))
- 61),trunc(uabopen_due_date)),greatest((trunc(to_date(:g_bussiness_post_date))
- 90),trunc(uabopen_due_date)),uabopen_balance,0))) bal_61_90,
      
sum(decode(uabopen_balance_ind,'n',0,decode(least((trunc(to_date(:g_bussiness_post_date))
- 91),trunc(uabopen_due_date)),greatest((trunc(to_date(:g_bussiness_post_date))
- 120),trunc(uabopen_due_date)),uabopen_balance,0))) bal_91_120,
      
sum(decode(uabopen_balance_ind,'n',0,decode(least((trunc(to_date(:g_bussiness_post_date))
- 121),trunc(uabopen_due_date)),greatest((trunc(to_date(:g_bussiness_post_date))
- 150),trunc(uabopen_due_date)),uabopen_balance,0))) bal_121_150,
      
sum(decode(uabopen_balance_ind,'n',0,decode(least((trunc(to_date(:g_bussiness_post_date))
- 151),trunc(uabopen_due_date)),greatest((trunc(to_date(:g_bussiness_post_date))
- 180),trunc(uabopen_due_date)),uabopen_balance,0))) bal_151_180,
      
sum(decode(uabopen_balance_ind,'n',0,decode(least((trunc(to_date(:g_bussiness_post_date))
- 181),trunc(uabopen_due_date)),greatest((trunc(to_date(:g_bussiness_post_date))
- 99999),trunc(uabopen_due_date)),uabopen_balance,0))) bal_181_99999
from   uabopen a, utracct b, utrsrat c
where  uabopen_business_post_date <=to_date(:g_bussiness_post_date)
and    utrsrat_srat_code=uabopen_srat_code
and    utrsrat_scat_code=uabopen_scat_code
and    trunc(sysdate) between utrsrat_effect_date and utrsrat_nchg_date
and    utracct_glcl_code=utrsrat_glcl_code
and    uabopen_balance_ind in ('n','p')
group by uabopen_cust_code,
        uabopen_prem_code,
        utracct_account_a,
        uabopen_bad_debt_status_code;

i hope the plan comes out in a decent format as i am having to use a
web browser to send emails today.

Thanks,
Ken

Plan
SELECT STATEMENT  CHOOSECost: 120,495  Bytes: 480,209,954
Cardinality: 4,754,554
        9 SORT GROUP BY  Cost: 120,495  Bytes: 480,209,954  Cardinality:
4,754,554
                8 HASH JOIN  Cost: 10,376  Bytes: 480,209,954  Cardinality: 
4,754,554                   
                        5 MERGE JOIN  Cost: 17  Bytes: 33,580  Cardinality: 730 
                
                                2 TABLE ACCESS BY INDEX ROWID TABLE 
UIMSMGR.UTRACCT Cost: 2
Bytes: 1,335  Cardinality: 89
                                        1 INDEX FULL SCAN INDEX 
UIMSMGR.UTRACCT_KEY2_INDEX Cost: 1
Cardinality: 89
                                4 SORT JOIN  Cost: 15  Bytes: 22,630  
Cardinality: 730          
                                        3 TABLE ACCESS FULL TABLE 
UIMSMGR.UTRSRAT Cost: 14  Bytes: 22,630
Cardinality: 730
                        7 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE 
UIMSMGR.UABOPEN Cost:
10,250  Bytes: 613,255,610  Cardinality: 11,150,102  Partition #: 8
                                6 INDEX RANGE SCAN INDEX 
UIMSMGR.UABOPEN_BUS_POST_DATE_INDEX Cost:
14,175  Cardinality: 2,023,858

On 1/10/07, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxxxxx> wrote:
No, I don't think it's a waste of time.  An FFS should be faster, if
you're going to read the entire index.  FFS does multi-block reads,
where the range scan will do single-block reads and walk through the
index structures.

What hint are you using?  INDEX_FFS?  How complex is the SQL?  Can you
post it along w/ execution plan?

-Mark


--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

There is nothing so useless as doing efficiently that which shouldn't be
done at all.  -Peter F. Drucker, 1909-2005


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ken Naim
Sent: Wednesday, January 10, 2007 5:17 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Index Range Scan vs Fast full scan

From a perfromence point of view is a fast full scan (ffs) of an index
faster than a index range scan when they both read the index fully? I am
trying to test this but i having diffiuclty to get my plan to use a ffs
even when hinted.

Context:
My btree index has only 2 values N and P and i dont use a bitmap as i
can't rebuild after every load as the table has 225M rows in it. the
index only has 7M rows as the rest of the values are null and a
particular query needs to access all 7m rows that the index refers to.
So both the ffs and the range scan read the same data. My question is am
I wasting my time trying to get the plan to do a ffs of the index?
--
//www.freelists.org/webpage/oracle-l



--
//www.freelists.org/webpage/oracle-l


Other related posts: