Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Re: Is this a bug or a limitation of the SQL language

Re: Is this a bug or a limitation of the SQL language

From: Joseph Hellerstein <hellers_at_wisc.edu>
Date: 26 Aug 92 21:27:13 GMT
Message-ID: <HELLERS.92Aug26152713@cleo.wisc.edu>

In article <BtLuFp.Kp6_at_watdragon.uwaterloo.ca> gnpaulle_at_maytag.uwaterloo.ca (Glenn Paulley) writes:

> > In article <BtJKIG.2K5_at_watdragon.uwaterloo.ca> gnpaulle_at_maytag.uwaterloo.ca (Glenn Paulley) writes:
> > > [I wrote:]
> > > |select * from emp
> > > |where salary between
> > > |(select salary from emp where ename = 'Larry')
> > > |and
> > > |(select salary from emp where ename = 'John')
 

> > > You want to avoid subqueries whenever possible. To get what you need,
> > > try the following:
> >
> However, the scalar subqueries above are not correlated, so
> I still fail to see how join processing could perform better than (basically)
> a single scan- nested loops aren't required. Again, I'm assuming that
> ename is indexed, or in some way it's possible to determine Larry
> and John's salary quickly.

You're right -- in this example, since the subqueries are scalar and not correlated, the expression above is as good as any other. My error. It's still a healthy knee-jerk reaction to convert subqueries to joins, but in this case it doesn't help.

Joe Hellerstein Received on Wed Aug 26 1992 - 16:27:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US