Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index Range Scan vs Fast full scan

Re: Index Range Scan vs Fast full scan

From: Ken Naim <kennaim_at_gmail.com>
Date: Wed, 10 Jan 2007 17:49:22 -0500
Message-ID: <539b29c0701101449m2934b16dkaef1a6cdab3e984@mail.gmail.com>


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_at_il.proquest.com> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ken Naim
> Sent: Wednesday, January 10, 2007 5:17 PM
> To: oracle-l_at_freelists.org
> 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?
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 10 2007 - 16:49:22 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US