Re: ** join query tuning
- From: A Joshi <ajoshi977@xxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx
- Date: Sun, 23 Oct 2005 19:32:25 -0700 (PDT)
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
--
//www.freelists.org/webpage/oracle-l
---------------------------------
Yahoo! FareChase - Search multiple travel sites in one click.
Other related posts:
