Re: Oracle using indexes

The problem is that Oracle has to be able to parse the statement before knowing anything about the values of the bind variables. If the statement had to be re-optimised for every change of the values of the bind variables, then there would be virtually no in using bind variables anyway.

Consequently Oracle has to make a very simplistic assumption about the numbers of rows identified by each bind variable, rather than using a histogram to determine whether it is 'going to be' a best case value or a worst case value.

In cases of EQUALITY, he effect is usually not too severe, in cases of RANGE SCANs, Oracle is reasonably likely to over-estimate the number of rows.

So for all range-scans, and for equalities based on highly skewed data, you need to pre-empt the default mechanism.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Pascal Glauser wrote in message <884pqc$ohq$1_at_nnrp1.deja.com>...
>Hi Jonathan
>
>Do you know the concepts of Oracle concerning reparsing queries with
>histogrammed columns ? As I understand your post, a query with a bind
>variable in the where clause is not re-optimized, even if the actual
>value of the bind-variable changes and there is a histogramm with this
>column. Is that correct ?
>
>This would be disappointing, since oracle-tools (forms, reports a.s.o.)
>create a lot of queries with bind-variables, obviously to take advantage
>of parsed queries in the shared-pool.
>
>Pascal Glauser
>
>In article <950308730.4699.0.nnrp-12.9e984b29_at_news.demon.co.uk>,
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>> ... skip
>>
>> If the data is very skewed (flags with 99 Yes's for each No)
>> then you need to include a histogram on that column with
>> the analyze (and write code that does not use bind variables
>> against that column).
>>
>> etc. etc. etc.
>>
>>
>> ... skip
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Sun Feb 13 2000 - 02:39:31 CST