Re: How to get Oracle to an Index without hints?

Well, PO_INDEX018 should be more attractive to Oracle...

• lower clustering_factor
• higher distinct_keys
• lower leaf_blocks
• lower data_blocks_per_key
• same blevel

Given that, I would say Oracle made the decision to use PO_INDEX016 from other factors. I'm still curious if it thought there was a tie, did you try renaming PO_INDEX016 to PO_INDEX999 to see if it would switch indexes?

Richard

Joerg Jost wrote:
>
> On Tue, 30 Apr 2002 20:00:26 GMT, Richard Kuhler <noone_at_nowhere.com>
> wrote:
>
> ...
> >I would
> >guess that the index statistics are skewing Oracle towards using the
> >undesired index (maybe a significantly lower clustering factor). Can
> >you post the index statistics?
> >
> >select index_name, blevel, leaf_blocks,
> > distinct_keys, avg_leaf_blocks_per_key,
> > avg_data_blocks_per_key, clustering_factor
> >from user_indexes
> >where index_name in ('PO_INDEX016', 'PO_INDEX018')
> >
> Hi Richard,
>
> here are the index statistics:
> INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS
> ------------------------------ ---------- ----------- -------------
> AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
> ----------------------- ----------------------- -----------------
> PO_INDEX018 2 6818 132486
> 1 6 877727
>
> PO_INDEX016 2 10825 95073
> 1 9 893648
>
> Can you see something wrong with the index PO_INDEX018.
>
> Thx for your help.
> Joerg Jost
Received on Fri May 03 2002 - 13:05:08 CDT