** join query tuning

From: A Joshi <ajoshi977_at_yahoo.com>
Date: Fri, 21 Oct 2005 12:18:51 -0700 (PDT)
Message-ID: <20051021191852.60611.qmail@web60711.mail.yahoo.com>

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.  

I have tried combination of use_nl( and index( hints but it does full table scan or does index full scan. I think index full scan is worser than full table scan. I have tried changing the order in the from and where clause without luck. What am I missing? Thanks for your help.                     

---

 Yahoo! FareChase - Search multiple travel sites in one click.
--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 21 2005 - 14:41:27 CDT