hi all:

I would appreciate any hint on how to deal with the following query:

select TABLE1.KEY, TABLE1.col1, TABLE1.col2, TABLE1.col3.
TABLE1.col4, TABLE2.col1, TABLE1.col5 TABLE1.col6
TABLE1.col7 TABLE1.col8
from TEMPTABLE,
TABLE1,
TABLE2
where TEMPTABLE.KEY = TABLE1.KEY
AND TABLE1.KEY = TABLE2.KEY (+)
order by TABLE1.KEY

TABLE1 and TABLE2 have about 40mil rows. TABLE1 is wider than TABLE2.
TEMPTABLE is smaller, about 5-10mil rows and is being recreated on the fly
by a tool. I have build indices on TABLE1, that includes all the fields
used in the query. I have an index on TABLE2 on KEY and col1. Currently
acording to an explain plan oracle does a hash join on
the TEMPTABLE and TABLE1 and then anothe hash join to include TABLE2. In
all cases it reads either full table or
full index. The sizes of the TABLE1 and TABLE2 are 15 and 10G. I don't
have any qualifications to reduce the number of rows (like where col1 = 1
or something like that). When I turn off hash joins, I am getting MERGE
SORT with again
full table/index scans. How can I improve the performnance here? I don't
even know where to start.

TIA
thank you

Gene Gurevich



Oracle LazyDBA home page