Re: ** join query tuning

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.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