Join order and intermediate results

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

--
http://www.freelists.org/webpage/oracle-l