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_at_centrexcc.com> 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.  
--
http://www.freelists.org/webpage/oracle-l