Re: Whitespace in SQL = hard parse?

koert54 wrote:
>
> I *thought* the hash is based on the first 200 ascii codes of the
> statement - so whitespace or CR are different
> ascii values - hence different hash/statement
>
> "Karsten Farrell" <kfarrell_at_belgariad.com> wrote in message
> news:MPG.18ac7402a50365a198968a_at_news.la.sbcglobal.net...
> > This morning I was explaining to a developer why bind variables are a
> > good thing. That led to a discussion of how Oracle first parses any SQL
> > to produce a hash, which it uses to determine if it had seen that
> > particular SQL before.
> >
> > I said that different white space caused a different hash and thus, a
> > new hard parse. Then I got to thinking. It was a very long time ago that
> > I attended an Oracle Education class (employers don't seem to be as
> > willing to pay for education these days). But when I did attend, the
> > instructor said that these two statements would cause a different hash:
> >
> > 1. select * from emp;
> > 2. select *
> > from emp;
> >
> > The only difference is the newline after the '*' in the second example.
> > Is it still true that these are considered "different" statements? Or
> > does Oracle now eliminate extra whitespace prior to calculating the
> > hash?
> >
> > Just curious. And while I'm at it, let me thank all the regulars who
> > post to the c.d.o NGs. I learn an awful lot just lurking. I'd be afraid
> > to list your names ... someone would complain because I listed another
> > poster before them ... and in reality, I've learned so much from each
> > and every one of you. Thanks again.
> >
> > --
> > /Karsten

I think the various methods Oracle used in the past: first 200 chars, first 64 chars, first and last sets of chars etc have all been obsoleted and the entire sql is now hashed

hth
connor Received on Thu Feb 06 2003 - 22:07:55 CST