Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_yahoo.com> 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/>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 21 2005 - 20:34:42 CDT