Re: Join order and intermediate results

Hi Dan,

please let me drop some comments to your interpretation of this ABCD puzzle.

>OK, now to the theoretical argument for why we should tend to avoid these
>many-to-many joins, using your case as an example. I'll make a couple of
guesses
>about the undescribed details of your case just to make this concrete, but
>alternate examples consistent with the information you provide should look
much
>the same:

I deduced a very complementary interpretation. To illustrate it, I will use an example from an insurance claim application: The both marginal tables A and D are very small, A is a claim type table, D a customer segment table. C is a customer table (large), B a claim table (larger).

So we have master - detail relationship on following tables: A -> B, D -> C and C-> B.

Lets assume following cardinality:

A (claim type) 100 records

B (claim) 10 million records

C (customer) 1 million records

D (customer segment) 100 records

Now assume there are very restrictive filters on both ends A and D resulting in only one row in each table.

So starting with the table D (customer segment) you have to process 10.000 customers and 100.000 claims on average. This is of course possible to do in nested loop but not very effective (at least measured in elapsed time), because (again on average) only 1.000 of the 100.000 processed claims fulfil the constraint on claim type (table A).

In an example like this I could imagine an execution plan of joining A to B resp. D to C in advance followed with the join of both result sets (resulting in a 10.000 times 100.000 records hash join with expected result of 1.000 rows).

>I *very* rarely find a query that needs a parallel plan to perform just
fine,
>however, once it is well tuned. More often, I find parallelism hiding just
how
>inefficient a non-optimal plan is, by throwing resources at it.

Even worst, there exist a class of queries with parallelism on; all of those queries can be tuned simple by deactivating or limiting the degree of parallelism:)

But of course "fine parallel plans" exists, although in most cases limited to a classical window oriented DW environment.

Regards,

Jaromir

• Original Message ----- From: "Dan Tow" <dantow_at_singingsql.com> To: "Smiley John - IL" <SMILEYJ_at_tusc.com> Cc: <oracle-l_at_freelists.org> Sent: Monday, October 04, 2004 2:22 AM Subject: Re: Join order and intermediate results

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