Join order and intermediate results
- From: Smiley John - IL <SMILEYJ@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 1 Oct 2004 10:07:38 -0500
Suppose we have four tables A, B, C, and D that are all used in a SELECT
statement. Let's further suppose that we know that the best way to join
these tables (best meaning fastest RT, and fewest LIOs) is to join A to B to
produce row source AB, join C to D to produce row source CD, then join AB to
CD.
How do I get Oracle to do this? At first I simply wrote the query as a
simple SELECT like this:
SELECT ...
FROM A, B, C, D
WHERE ...
When that didn't work and I made sure the table, index, and column stats
were correct, I tried various hints for join order and join method. When
that didn't work, I decided to smack Oracle in the face with the answer like
this:
SELECT ...
FROM (SELECT ... FROM A, B WHERE ...) AB, (SELECT ... FROM C, D WHERE ...)
CD
WHERE ...
However, it stubbornly refused to join in the correct order and I had to
result to storing the intermediate results in GTTs and then joining the
GTTs.
This is not a very satisfying means of solving the problem. Oracle seems to
doggedly follow a linear path for satisfying the query, which goes something
like this: join two row sources to create an intermediate result, pick
another row source and join it to the intermediate result to produce a new
intermediate result, repeat until done (in the case of parallel query, each
PQ slave seems to follow this same approach and then the query coordinator
merges the results).
Expressed as a tree, it looks like this (where / | and \ are join
operations):
D (or C)
/
C (or D)
/
A
|
B
When what I want is this:
Result
/ \
A C
| |
B D
In the case I'm describing, this makes the difference between a response
time of 0.1 seconds with 85 LIOs vs. a response time of 120 seconds with
60,000 LIOs.
I have a feeling I'm going to be smacking my forehead on this one, but I
just don't see what I'm missing.
John Smiley
P.S. This is 9.2.0.4
--
//www.freelists.org/webpage/oracle-l
Other related posts:
