I'm having some problems with a join query that performs extremely badly. Syntax looks a little like this:
Select a.column1, a.column2, ......, a.column5, b.column1, b.column2, ....., b.column5, c.column1, c.column2, ......, c.column5
from table1 a, table2 b, table3 c
where a.column1 = b.column3
and b.column2 = c.column4
I'm going to run through some basic checks tomorrow (i.e. are the joined fields indexed etc), but the problem is that the SQL itself is written to Oracle by a second application (Unica Affinium Campaign), so I have very little control over the syntax of the statement.
Two quick questions for now:
1) Is performance likely to be any better if the statement were written with an expicit INNER JOIN?
2) Is there any benefit in writing an ORDERED hint with only three tables? (Oracle is configured to use CBO rather than RBO).
Ensure that there are indeed indexes for what you are trying to join. Find the table and will limit the number of rows the quickest and use that as the DRIVING table. Ensure that your statistics are updated on table and indexes. Do the indexes have a true representation of the data or is the data skewed on one side of the index or the other? (If so, analyze the table for all indexed columns). Try setting autotrace on. alter session set optimizer_goal ... choose, first_row, all_rows, rule.... and see what you like better.... As mentioned above, post your explain plan with the number of rows from the tables and indexes that "should" be involved.