Re: How do you force the order?

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 - 09:09:03 CST