Re: ** join query tuning

Breitling,
   Thanks. Yes I tried these. The order does change but the second third table 
do not use indexes as 'index range' or 'index unique' scan. The small table 
does do a 'index full scan'. The big two tables stay with 'full table scan'. If 
I give a index( hint they do a 'index full scan'.
I tried removing the 'and x.noindcol = 'a' ' part but that did not help.
The indexes are fine and are used by individual one table queries with where 
clause. Even in this query if I add 'and x.conna = 123' all three tables use 
the right index and the query executes very fast.
 
To answer question from Arul in another e-mail : Are the indexes composite 
indexes on columns conna and connb ? If so, are they in the desired order?  All 
the indexes here are one column indexes. 
 
Thanks for your help.


Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:

Have you tried a leading or ordered hint?

select /*+ leading(z) */
x.cola, y.colb, z.colc
from tabz z, tabx x, taby y
where x.conna = y.conna
and x.connb = z.connb
and x.noindcol = 'a'

select /*+ ordered */
x.cola, y.colb, z.colc
from tabz z, tabx x, taby y
where x.conna = y.conna
and x.connb = z.connb
and x.noindcol = 'a'

At 01:18 PM 10/21/2005, 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.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com 

--
http://www.freelists.org/webpage/oracle-l

                
---------------------------------
 Yahoo! FareChase - Search multiple travel sites in one click.

Other related posts: