Re: ** join query tuning
- From: Paul Drake <bdbafh@xxxxxxxxx>
- To: ajoshi977@xxxxxxxxx
- Date: Fri, 21 Oct 2005 15:46:40 -0400
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.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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
