Re: Query Optimization

Why do you need the IN clause - isn't that going to be ensured by the join between c and d. In toher words, this query should give identical results:

select DISTINCT a.name, b.add, c.tele, d.code from nametable a, addtable b, teletable c, smallist d where d.id = c.id AND

c.id = b.id AND
b.id = a.id AND
b.id2 = a.id2

and should run at least an order of magnitude faster.

Ashish

"Rich Pinder" <rpinder_at_usc.edu> wrote in message news:3C8EB185.29B24A7E_at_usc.edu...
> Dont know if this list is the proper place to ask this, but here goes:
>
> I've got a select statement that runs SO slowly, and I'm sure it's
> because I'm missing something about select syntax that Oracle uses.
>
> here's a simple description:
> I have very large tables for NAMETABLE, ADDTABLE, AND TELETABLE.
> I have tiny table for SMALLIST.
>
> the sql i use must be interpreted by the optimizer to join all the large
> tables first, then do the sub'select on the very small table.
>
> What I want to do is get the small table processed first (this will
> surely make things run much faster).
>
> heres the select:
>
> select DISTINCT a.name, b.add, c.tele, d.code
> from nametable a, addtable b, teletable c, smallist d
> where d.id = c.id AND
> c.id = b.id AND
> b.id = a.id AND
> b.id2 = a.id2 AND
> c.id IN
> (select id from smallist); <---- this is the small table
>
>
> Thanks for any thoughts.
>
> rich
Received on Wed Mar 13 2002 - 09:32:15 CST