Re: Oracle using indexes

A copy of this was sent to Pascal Glauser <glauser_at_my-deja.com> (if that email address didn't require changing) On Sat, 12 Feb 2000 23:19:41 GMT, you wrote:

>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 ?
>

That is correct. if you parse "select * from T where c = :x", it'll use 'common sense' for how to optimize around :x -- it does not have a good way to optimize otherwise.

>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.
>

In any of these environments, you can override this behaviour and put in literals yourself and/or do dynamic sql. You have to look at the most commonly preferred method people would want to use. In 999 times out of 1,000 -- bind variables are absolutely the way to do it (think how disappointing it would be if forms *did not* use bind variables then). It is the exception to want to do it the other way. Mostly in Data warehousing and other more specialized environments -- with their own set of specialized tools. Given that in most cases with forms and reports -- if you didn't use bind variables you would find between 30-80% of your run time is PARSE time without bind variables -- the default behaviour is the correct one (in my opinion anyway).

>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.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Feb 13 2000 - 09:16:20 CST