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

Other related posts: