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
(Off-topic note: The regex will match only valid email addresses,
although there are some unusual addresses that will not be matched. It's
good enough for validating e-mail address input, if the purpose is to
warn and not enforce).
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...
Regards,
Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: [email protected]
Telefon: 08-761 06 55
Mobil: 0733-44 24 64
