This depends on which optimizer you are using.
If you are using rule based optimizer, the driving table by default is the
LAST table in the from clause.
If you are using cost based optimizer, it is the FIRST table, and you can
use hints to influence the behavior of the optimizer
Using a subquery probably makes things worse. If you want to go that route,
use exists as much as possible.
Hth,
--
Sybrand Bakker, Oracle DBA
Paul Bennett <bennett_at_cc.gatech.edu> wrote in message
news:3853E88A.BAEC7A10_at_cc.gatech.edu...
> 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 Sun Dec 12 1999 - 16:02:58 CST
