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

From: Joerg Jost <jost_at_unitrade.com>
Date: Fri, 03 May 2002 10:43:50 +0200
Message-ID: <d3j4dukv9i20g9ob86rbqiia698u4s7lpi@4ax.com>

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 - 03:43:50 CDT