Re: Index Range Scan vs Fast full scan

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,349
11 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: 2
Bytes: 1,335  Cardinality: 89
1 INDEX FULL SCAN INDEX UIMSMGR.UTRACCT_KEY2_INDEX Cost: 1
Cardinality: 89
4 SORT JOIN Cost: 13 Bytes: 22,630 Cardinality: 730 3 TABLE ACCESS FULL TABLE UIMSMGR.UTRSRAT Cost: 12 Bytes: 22,630
Cardinality: 730
9 PARTITION RANGE ALL Cost: 113,872 Bytes: 21,198,155
Cardinality: 385,421  Partition #: 8  Partitions accessed #1 - #4
                                8 INLIST ITERATOR
7 TABLE ACCESS BY LOCAL INDEX ROWID TABLE UIMSMGR.UABOPEN Cost:
113,872  Bytes: 21,198,155  Cardinality: 385,421  Partition #: 8
Partitions accessed #1 - #4
6 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 Corporation
http://www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l


Other related posts: