Den 2013-09-19 19:07 skrev Leyne, Sean såhär:

>
> > >I do expect more than on PLAN dues to the derived table and the union,
> > >but there seems to be a comma missing before the PLAN on line 05 at
> least.
> > >
> > >I don't really need to understand the plan, but I wanted to bring it up
> > >in case there's a bug that needs to be fixed...
> >
> > Hi Kjell, I doubt this is an error. The place you think a comma is
> missing, is
> > where your UNION is. I must admit that I've never before really noticed
> > whether or not there is a comma in the plan with union, but when I try
>
> Adding to Set's typically excellent comments, I would suggest that you
> look at using Common Table Expression (CTE) for your FE Företag inner
> join.
>
> The reason that the PLAN is showing a NATURAL access for FE Företag is
> because the SELECT from Företag is not getting the JOIN criteria,
> using a CTE would change that.
>

Strangely enough I SET's reply hasen't shown up in my inbox, and yes I
did check the spam/garbage folder. Anyway, I know separate subplans are
enumerated without comma, but it seems odd, then, that it doesn't prefix
*all* subplans with "PLAN". Shouldn't it appear like this?

01_PLAN SORT (
02___JOIN (
03_____JOIN (
04_______(
05_________PLAN FE Företag NATURAL
06_________PLAN JOIN (
07___________FE BIADR NATURAL,
08___________FE BI INDEX (IX_PK_Befattningsinnehav),
09___________FE BEF INDEX (IX_PK_Befattning)
10_________)
11_______),
12_______F INDEX (IX_PK_Företag),
13_______E INDEX (IX_PK_Epostadress),
14_______STATUS INDEX (IX_PK_Företagsstatus)
15_____),
16_____JURFORM INDEX (IX_PK_JuridiskForm),
17_____BVSTATUS INDEX (IX_PK_BVstatus)
18___)
19_)

The lines 05-10 is the plan returned if only the derived table part is
executed alone (minus the FE prefixes for the table aliases of course).
Putting a paranthesis around that subplan makes sense, and makes the
wrapping join contain a correctly comma separated list of "items".
Without the paranthesis it's not clear that line 05 and lines 06-10 are
subplans that are unioned, and then joined.

At the least, it seems incorrect that line 05 is missing the "PLAN" prefix.

Regards,
Kjell

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64