| Subject | Where's my logic flawed? |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2016-06-03T08:27:05Z |
This query:
WITH TMP( TJENESTEENHETRESHID, TJENESTEENHETLOKAL, IMPORT_DATO ) AS
( SELECT TJENESTEENHETRESHID, TJENESTEENHETLOKAL, MAX( IMPORT_DATO )
FROM NPR
WHERE TJENESTEENHETRESHID > 0
AND TJENESTEENHETLOKAL > ''
GROUP BY 1, 2 ),
TMP2( TJENESTEENHETRESHID, TJENESTEENHETLOKAL ) AS
( SELECT TJENESTEENHETRESHID, TJENESTEENHETLOKAL
FROM TMP T
WHERE NOT EXISTS( SELECT * FROM TMP T2
WHERE T.TJENESTEENHETRESHID = T2.TJENESTEENHETRESHID
AND T.IMPORT_DATO < T2.IMPORT_DATO ) )
SELECT TJENESTEENHETRESHID, LIST( TJENESTEENHETLOKAL ) TJENESTEENHETLOKAL
FROM TMP2
GROUP BY 1
HAVING COUNT( DISTINCT TJENESTEENHETLOKAL ) = 1
generates this plan:
PLAN SORT ((TMP2 T2 NPR NATURAL))
PLAN SORT ((TMP2 T NPR NATURAL))
NPR is a medium sized table (I'm guessing 20 million rows), but the result of the first CTE is only 2200 rows. None of the fields in this particular query are indexed. I tried to run the query, but decided to terminate it after about 2 hours 40 minutes.
If I run the first CTE separately
( SELECT TJENESTEENHETRESHID, TJENESTEENHETLOKAL, MAX( IMPORT_DATO )
FROM NPR
WHERE TJENESTEENHETRESHID > 0
AND TJENESTEENHETLOKAL > ''
GROUP BY 1, 2 )
it takes just over 16 minutes. Putting the result of this CTE in a temporary table and running the rest of the query against this temporary table only takes a couple of seconds (reducing the 2200 rows to 1600). The way I normally read plans seems to fail me this time - I don't quite understand why NPR is mentioned twice in the plan (I assume it has to do with my "two-level" GROUP BYs). Can anyone explain why? Or better: Does anyone understand why the query takes so long to execute?
Thanks in advance for any hints,
Set