Re: using bind variables makes the optimizer choose a bad plan

prunoki wrote:
> Hi,
>
> Given the query:
>
> select * from carhitelszerzodesek where flotta_id=:1
>
> The plan is table access full, though we have an index on flotta_id.
> Flotta_id is numeric, the table is analyzed with all columns, all
> indexes. If I hard code the value
>
> select * from carhitelszerzodesek where flotta_id=0 is table access
> full.
>
> select * from carhitelszerzodesek where flotta_id=1 is index by rowid.
>
> These results are correct, because 99% of the flotta_id column is 0. I
> cannot use hints, because this sql is generated by a RAD tool. Oracle
> is 8.1.7.4. Is there any way besides hints to change this behaviour?
>
> Regards,
>
> Hegyvari Krisztian

This may or may not help but ...

www.psoug.org
click on Morgan's Library
click on Indexes
Scroll down to the demo on Function Based Indexes with Tom Kyte's name next to it.

With data such as yours it may make no sense to index the 0 values.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)