Re: ** join query tuning

Are the indexes composite indexes on columns conna and connb ? If so, are
they in the desired order?
 Arul

 On 10/21/05, A Joshi <ajoshi977@xxxxxxxxx> 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.noindcoldoes 
> 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://us.lrd.yahoo.com/_ylc=X3oDMTFqODRtdXQ4BF9TAzMyOTc1MDIEX3MDOTY2ODgxNjkEcG9zAzEEc2VjA21haWwtZm9vdGVyBHNsawNmYw--/SIG=110oav78o/**http%3a//farechase.yahoo.com/>
>
>

Other related posts: