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,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
--
//www.freelists.org/webpage/oracle-l




Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com 
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2007