Re: ** join query tuning

Have you tried a leading or ordered hint?

select /*+ leading(z) */

    x.cola, y.colb, z.colc
from tabz z, tabx x, taby y
where x.conna = y.conna

   and x.connb = z.connb
   and x.noindcol = 'a'

select /*+ ordered */

    x.cola, y.colb, z.colc
from tabz z, tabx x, taby y
where x.conna = y.conna

   and x.connb = z.connb
   and x.noindcol = 'a'

At 01:18 PM 10/21/2005, A Joshi wrote:
>Hi,
> I have three tables : tabx, taby, tabz. tabx and taby are huge
> and have around 10 million rows. tabz is small and has 1 million.
>
>query :
>
>select tabx.cola, taby.colb, tabz.colc
>from tabx, taby, tabz
>where tabx.conna = taby.conna
>and tabx.connb = tabz.connb
>and tabx.noindcol = 'a'
>
>The connecting columns conna and connb have indexes. the
>tabx.noindcol does not have index.
>
>Ideally I would like to start with full table scan for the small
>table tabz then go to tabx using index filter then go to taby using
>index. One full table scan is unavoidable.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l