Paul, The db version is 184.108.40.206 Thanks Paul Drake <bdbafh@xxxxxxxxx> wrote: On 10/21/05, 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. > > 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. > "What am I missing?" Oracle database server version information, for starters. Pd -- http://www.freelists.org/webpage/oracle-l --------------------------------- Yahoo! FareChase - Search multiple travel sites in one click.