Re: Join order and intermediate results

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Sun, 3 Oct 2004 21:51:26 +0200
Message-ID: <0bfe01c4a982$6085a370$3c02a8c0@JARAWIN>

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.

| 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_at_tusc.com> To: <oracle-l_at_freelists.org> Sent: Friday, October 01, 2004 5:07 PM Subject: Join order and intermediate results

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

Received on Sun Oct 03 2004 - 14:50:23 CDT