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

From: Joerg Jost <jjost_at_nikocity.de>
Date: Thu, 02 May 2002 21:05:22 +0200
Message-ID: <aas2l0$r4u$07$1@news.t-online.com>

Richard Kuhler wrote:

> I played with some data and was unable to get Oracle to exhibit this
> type of behavior.  I'm intrigued as to what's causing it.  Can you post
> the column statistics as well as the index statistics?
> 
> select column_name, num_distinct, num_nulls,
>   num_buckets, density
> from user_tab_col_statistics
> where table_name = 'PO'
>   and column_name in ('POS_MWBT', 'PO_AT_AT_NO',
>     'PO_AT_AT_SE_SE_AG_AG_NO', 'PO_AT_AT_SE_SE_AG_AG_WG',
>     'PO_AT_AT_SE_SE_NO', 'POS_ART')
> 
> 
> I'd also be interested to know if Oracle thinks there's a tie between
> these indexes.  Can you try renaming PO_INDEX016 to PO_INDEX999 and
> explain the query again (Oracle breaks ties by index name)?
> 
> Richard
> 

Hello,

sorry for the late answer, but our newsserver in the company does not show me your answers until now. I will try your suggestions and post the results here.

> Richard Kuhler wrote:

>>
>> Since bind variables are being used, I don't believe Oracle can
>> calculate selectivity (no column stats or histograms are used). As I
>> read it, "the optimizer heuristically guesses a small value for the
>> selectivity." Furthermore, each expression should compound the
>> selectivity for the index prefixed columns. Given that, Oracle should
>> desire the addition of the POS_MWBT expression with that index. 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')
>>
>> One other possibility is that Oracle has just given up... "the plan
>> generator uses an internal cutoff to reduce the number of plans it
>> tries". However, I doubt that's the case given the simplicity of this
>> query.
>>
>> Richard
>>

And i also post the results of this SQL tomorrow.

>> > - analyzing the index (sounds obvious, but is it done?)
Yes, we analyze the table with the following statement: ANALYZE TABLE po
COMPUTE STATISTICS FOR ALL INDEXES FOR ALL INDEXED COLUMNS FOR TABLE;
>> > - using histograms on your index (is your data evenly distibuted?)
>> > - rewriting the where clause to:
>> > WHERE ( PO_AT_AT_NO = :2 )
>> > AND ( PO_AT_AT_SE_SE_NO = :5 )
>> > AND ( PO_AT_AT_SE_SE_AG_AG_NO = :3 )
>> > AND ( PO_AT_AT_SE_SE_AG_AG_WG = :4 )
>> > AND ( POS_MWBT = :1 )
>> >
>> > provided your tool allows you to do that.
>> > If all else fails - replace tool!

Yes, we are able to change the where clause to your suggestion. And another Info, i forgot to tell the Oracle - Version we use It is, sorry for that, Oracle 7.3.4 and we are not able to change it in a short time, because the OS used by the customer don´t supports any higher version of Oracle.
As far as i know, with our installation of Oracle 8i(8.1.6 or 8.1.7) we don´t have this problem. But i try to get sure of this point, if i am back in the company :-)
Thx a lot for your help.
Jörg Jost Received on Thu May 02 2002 - 14:05:22 CDT