Re: Query Optimization

Hello,

If you had not try yet, you could add the "ordered" optimizer hint at the beginning of the query, as following:
select /*+ ordered */ DISTINCT a.name, b.add, c.tele, d.code Then, in the FROM clause, invert tables order, to put the smallest first, because it's much better to join all the SMALL tables first, then do the sub'select on the very LARGE table!!:
FROM smallist d, teletable c, addtable b, nametable a This last clause suppose that the smallist table is the smalliest, and nametable the biggest.

Hope this helps.
Regards.

"Rich Pinder" <rpinder_at_usc.edu> a écrit dans le 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 - 05:13:33 CST