Re: Query Optimization

What version of Oracle?
What is the explain plan?
What indexes exist?
Are the tables and indexes analyzed?
Try:

 select 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) group by a.name,b.add,c.tele,d.code

If you really need the distinct the database compares every row with every other row so remove the distince or use a group by. You haven't supplied enough information but to take a wild assed guess. Jim

"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 - 07:45:26 CST