Browse oracle-l: This Month's Archive • Main Archive Page • Related posts • Previous by Date • Next by Date

Re: Join order and intermediate results

From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Sun, 3 Oct 2004 21:51:26 +0200

Hi John,



> However, it stubbornly refused to join in the correct order and I had to
> cresult to storing the intermediate results in GTTs and then joining the
> GTTs.



use NO_MERGE hint to force optimiser to perform this task. Something like
this:



SELECT ...
FROM (SELECT /*+ NO_MERGE */... FROM A, B WHERE ...) AB,

     (SELECT /*+ NO_MERGE */  ... FROM C, D WHERE ...) CD
WHERE ...



You get a plan as follows, that is exactly what you want.

--------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |  1000 | 39000 |       |   731  (26)|

|*  1 |  HASH JOIN           |      |  1000 | 39000 |       |   731  (26)|

|   2 |   VIEW               |      |  1000 | 26000 |       |   116  (39)|

|*  3 |    HASH JOIN         |      |  1000 |  5000 |       |   116  (39)|

|   4 |     TABLE ACCESS FULL| D    |     1 |     1 |       |     4   (0)|

|   5 |     TABLE ACCESS FULL| C    |   100K|   390K|       |    93  (28)|

|   6 |   VIEW               |      |   100K|  1269K|       |   595  (21)|

|*  7 |    HASH JOIN         |      |   100K|   878K|  1568K|   595  (21)|

|   8 |     TABLE ACCESS FULL| A    |   100K|   390K|       |    93  (28)|

|   9 |     TABLE ACCESS FULL| B    |   100K|   488K|       |    93  (28)|

--------------------------------------------------------------------------



But be careful, in my opinion the problem of merging of subqueries is
definitely *not* the core of this problem.

The problem can be reduced to a situation, where you have three tables let
say  D1,F,D2 (note that F stand for fact, D for dimension) and you know:

* the join of all three tables gives only few rows, but

* both joins D1,F and D2,F gives large results



I call it a "where to start" problem. I assume in your case there is on one
side one join more due to some dimension hierarchy.

The simplest solution to this dilemma is a "brute force" approach: use hash
join and let the optimiser do the selection.

There are of course more intelligent approaches. Oracle tried so solve this
with star join in the past (leveraging what you call "meaningless Cartesian
product" of the dimensions), more recently with the star transformation -
both of those strategies are direct opposite to the one-table-at-a-time join
rule.



As far as theory; I haven't really experience with this star stuff, having
bad luck always missing some preconditions to deploy those features:)



regards



Jaromir D.B. Nemec

----- Original Message ----- 
From: "Smiley John - IL" <SMILEYJ@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, October 01, 2004 5:07 PM
Subject: Join order and intermediate results


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

References:

Join order and intermediate results
From: Smiley John - IL

Re: Join order and intermediate results

Other related posts: