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