>I tried running a query in FlameRobin:
>
>select F."Löpnr",
> F."Orgnr",
> F."Namn",
> FE."Fält",
> E."Adress"
>from "Företag" F
>inner join "Företagsstatus" Status on Status."ECO_ID" = F."Status"
>inner join "JuridiskForm" JurForm on JurForm."ECO_ID" = F."JuridiskForm"
>inner join "BVstatus" BVstatus on BVstatus."ECO_ID" = F."BVstatus"
>inner join (
> select "ECO_ID" "FöretagId",
> "Epostadress" "EpostadressId",
> 'Info' "Fält"
> from "Företag"
> union
> select BI."Företag" "FöretagId",
> BIadr."Epostadress" "EpostadressId",
> Bef."Namn" "Fält"
> from "Befattningsinnehav" BI
> inner join "Befattningsinnehavsadress" BIadr on
>BIadr."Befattningsinnehav" = BI."ECO_ID"
> inner join "Befattning" Bef on Bef."ECO_ID" = BI."Befattningar"
> ) FE on FE."FöretagId" = F."ECO_ID"
>inner join "Epostadress" E on E."ECO_ID" = FE."EpostadressId"
>where Status."Kod" in ('1', '2')
> and BVstatus."Kod" in ('0', '1', '6', '7', '8')
> and JurForm."Kod" <> '91'
> and E."Adress" not similar to
>'(([\-0-9A-Z\_a-z]+(.[\-0-9A-Z\_a-z]+)*)|("[^"]+"))@((\[[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}\])|(([a-zA-Z\-0-9\_]+.)+([a-zA-Z]{2,3}|info|aero|name)))'
>escape '\'
>order by F."Löpnr", case when FE."Fält" = 'Info' then ' ' else FE."Fält" end
>
>Anyway, FlameRobin reports this plan, which I find somewhat weird:
>
>PLAN SORT (JOIN (JOIN ((FE Företag NATURAL)
>PLAN JOIN (FE BIADR NATURAL, FE BI INDEX (IX_PK_Befattningsinnehav), FE
>BEF INDEX (IX_PK_Befattning)), F INDEX (IX_PK_Företag), E INDEX
>(IX_PK_Epostadress), STATUS INDEX (IX_PK_Företagsstatus)), JURFORM INDEX
>(IX_PK_JuridiskForm), BVSTATUS INDEX (IX_PK_BVstatus)))
>
>Trying to reformat and indent (using _ to make sure indent is preserved):
>01_PLAN SORT (
>02___JOIN (
>03_____JOIN (
>04_______(FE Företag NATURAL)
>05_______PLAN JOIN (
>06_________FE BIADR NATURAL,
>07_________FE BI INDEX (IX_PK_Befattningsinnehav),
>08_________FE BEF INDEX (IX_PK_Befattning)
>09_______),
>10_______F INDEX (IX_PK_Företag),
>11_______E INDEX (IX_PK_Epostadress),
>12_______STATUS INDEX (IX_PK_Företagsstatus)
>13_____),
>14_____JURFORM INDEX (IX_PK_JuridiskForm),
>15_____BVSTATUS INDEX (IX_PK_BVstatus)
>16___)
>17_)
>
>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
select * from rdb$database
union
select * from rdb$database
the reported plan (in Database Workbench) is:
PLAN (RDB$DATABASE NATURAL)
PLAN (RDB$DATABASE NATURAL)
i.e. no comma. By the way, I hope neither Företag nor Befattningsinnehavsadress
contains millions of records. If they do, it might be possible to rewrite your
query to something that executes quicker, but I think that would require a bit
more knowledge about the tables in your subselect.
HTH,
Set
