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

That's one of the trade-offs when deciding to use bind variables with highly skewed data. When dealing with bind variables, the optimizer has no choice but to assume an even distribution of data values. So, as in everything else, the answer to the question of whether to use bind variables is "it depends."

Question: what is the performance like when you are selecting on that 1% of the data that "should" be selected by index? Received on Thu Sep 08 2005 - 07:33:32 CDT