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

Hello Jonathan,

Jonathan Lewis wrote:

>
> One quick thought - are you sure that the
> types of the bind variables match the types
> of the columns - in particular the bind variable
> for
> >> WHERE ( POS_MWBT = :1 )
>

Yes, the programm takes care of the variable - types.
>
> Check also the avg_data_blocks_per_key
> and avg_leaf_blocks_per_key for the twi
> indexes, and the clustering_factor.

I will do it as far as our newsserver in the company gets your answers :-(

>
> If these are unrealistic you could try using
> dbms_stats.set_index_stats to write
> suitable stats to the indexes. (Much cheaper
> than running analyze every night).
>

I donīt know, wheather Oracle 7.3.4 is able to do this, but if, i try it shortly, thx.  

> At what level are you running the analyze ?
> estimate, estimate percentage, compute ?
>

The SQL Statement is as follows:
ANALYZE TABLE po
COMPUTE STATISTICS FOR ALL INDEXES FOR ALL INDEXED COLUMNS FOR TABLE;  
> How accurate are the low/high values on
> user_tab_columns that you get for the
> 5 column in the where clause ?
>

I will check it out.
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> Next Seminar - Australia - July/August
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>

Thx for your help
Joerg Jost Received on Thu May 02 2002 - 14:12:48 CDT