Re: Whitespace in SQL = hard parse?

Karsten Farrell wrote:
>
> 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

white space is different - although there are some idiosyncracies - I think stored plan outline comparision now avoids white space and ignores case in some/all circumstances. Similarly, some tools likes Forms etc convert all their SQL's to upper case to increase the chances of sharing

hth
connor Received on Thu Feb 06 2003 - 22:09:20 CST