Re: Index Range Scan vs Fast full scan
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: kennaim@xxxxxxxxx
- Date: Wed, 10 Jan 2007 19:09:57 -0700
What Oracle version/release??What is your db_file_multiblock_read_count set at? System statistics (provided Oracle > 8i)?
As Jonathan keeps saying - and demonstrating - you need 2 hints per table in order to straightjacket the CBO enough to do what you want. According to that ROT you are 5 hints short.
Can you show the plan using the dbms_xplan package (again provided Oracle > 8i).
What does a 10053 trace say? At 03:56 PM 1/10/2007, Ken Naim wrote:
I must be having a bad day now my copy and paste had the wrong thing in the buffer. Plan SELECT STATEMENT CHOOSECost: 119,032 Bytes: 16,599,249 Cardinality: 164,34911 SORT GROUP BY Cost: 119,032 Bytes: 16,599,249 Cardinality: 164,349 10 HASH JOIN Cost: 113,891 Bytes: 16,599,249 Cardinality: 164,349 5 MERGE JOIN Cost: 15 Bytes: 33,580 Cardinality: 730 2 TABLE ACCESS BY INDEX ROWID TABLE UIMSMGR.UTRACCT Cost: 2Bytes: 1,335 Cardinality: 891 INDEX FULL SCAN INDEX UIMSMGR.UTRACCT_KEY2_INDEX Cost: 1Cardinality: 894 SORT JOIN Cost: 13 Bytes: 22,630 Cardinality: 730 3 TABLE ACCESS FULL TABLE UIMSMGR.UTRSRAT Cost: 12 Bytes: 22,630Cardinality: 7309 PARTITION RANGE ALL Cost: 113,872 Bytes: 21,198,155Cardinality: 385,421 Partition #: 8 Partitions accessed #1 - #4 8 INLIST ITERATOR7 TABLE ACCESS BY LOCAL INDEX ROWID TABLE UIMSMGR.UABOPEN Cost:113,872 Bytes: 21,198,155 Cardinality: 385,421 Partition #: 8 Partitions accessed #1 - #46 INDEX RANGE SCAN INDEX UIMSMGR.UAB_OPEN_BALANCE_ID_INDEX Cost:4,714 Cardinality: 7,708,413 Partition #: 8 Partitions accessed #1 - #4 this last line in the plan is what i am trying to change -- http://www.freelists.org/webpage/oracle-l
Regards Wolfgang Breitling Centrex Consulting Corporationhttp://www.centrexcc.com