| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index Range Scan vs Fast full scan
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_codeand 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-lReceived on Wed Jan 10 2007 - 16:49:22 CST
 |
 |