Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index Range Scan vs Fast full scan

Re: Index Range Scan vs Fast full scan

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 10 Jan 2007 19:09:57 -0700
Message-Id: <200701110209.l0B29Ru6015966@mail56.megamailservers.com>


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
Received on Wed Jan 10 2007 - 20:09:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US