Re: How do you force the order?

Did you try the ordered hint?

SELECT /*+ ORDERED */

       ....
  FROM ....
 WHERE ....;

In article <38552E38.C9284EF7_at_cc.gatech.edu>,   Paul Bennett <bennett_at_cc.gatech.edu> wrote:
> 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.
>
>

--
Erik
Consultant
Saraswati Systems Corporation - (SSC)

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 13 1999 - 14:48:56 CST