Re: How do you force the order?

Actually, that is what I am doing. The problem is that the where is being applied to the large table BEFORE the join limits the number of rows. So, in your example x.b = 2 is being done all all rows in x, and then those rows are being joned to y.

How do I tell it what order to do the where in?

• Paul

Brian Peasland wrote:

> Paul,
>
> I've often run into the problem similar to this that I've been able to
> fix using "inline" views. Try SELECT *
> FROM large x, (SELECT * FROM small WHERE small.b = '1') y
> WHERE x.a = y.a AND x.b = '2';
>
> This will pull only a subset from the small table.
>
> HTH,
> Brian
>
> Paul Bennett wrote:
> >
> > I have two tables that I am joining, both are about the same size, but I
> > am naming them below as large and small because when the where is
> > applied to the JUST the large table, it returns a large set, when the
> > where is applied to the small table, it returns a small set.
> >
> > SELECT *
> > FROM small, large
> > where small.a = large.a and
> > small.b = '1' and
> > large.b = '2';
> >
> > What I want to do it tell oracle to do the where for the small table
> > first, because it is going to limit the number of rows dramatically,
> > then i want it to take that set and do the where on the large table
> > (which now is a set of only a few rows)
> >
> > To clarify, I don't want the large.b = '2' to limit rows until after
> > small.b = '1' limits the rows. do I have to write the select like this?
> >
> > SELECT * FROM large
> > where large.a IN (Select a from small where small.b = '1') and large.b =
> > '2'
> >
> > Does this make sense? Basically, I have SQL statement that is slow
> > because it is comparing several columns from an entire table instead of
> > comparing them to a set of rows limited by a join.
> >
> > Thanks.
Received on Mon Dec 13 1999 - 11:34:16 CST