I have a very large non-partitioned table that is taking a long time to return results from this query:
SELECT
(SELECT Listagg(VALUE_STRING, ', ')
WITHIN GROUP (ORDER BY VALUE_STRING) AS VALUE_STRING
FROM ATTRIBUTE_DATA AD
WHERE AD.id = DT.dataid
AND AD.defid = 3070056
AND AD.attrid = 4
AND vernum = (SELECT Max(vernum)
FROM ATTRIBUTE_DATA AD
WHERE AD.id = DT.dataid
AND AD.defid = 3070056
AND AD.attrid = 4)
AND defvern = (SELECT Max(defvern)
FROM ATTRIBUTE_DATA AD
WHERE AD.id = DT.dataid
AND AD.defid = 3070056
AND AD.attrid = 4)
GROUP BY AD.id) AS PlantNumbers
FROM WORKTABLE W
left outer join CUSTOM_FORMS FORMS
ON FORMS.volumeid = w.subwork_subworkid
AND FORMS.versionnum = -1
AND FORMS.rowseqnum = 1
left outer join DATATREE DT
ON DT.name = FORMS.document_number_text_field
AND DT.ownerid = -2000
AND DT.SUBTYPE = 144
AND DT.dataid NOT IN (SELECT dataid
FROM dtreeancestors
WHERE ancestorid = 3109628)
When I take just this subquery alone it takes 12 minutes to run:
SELECT Max(vernum)
FROM ATTRIBUTE_DATA AD
WHERE AD.id = DT.dataid
AND AD.defid = 3070056
AND AD.attrid = 4
How can I improve this script.
Vernum
could that be the issue – Batman Feb 13 '18 at 22:31vernum
and another just ondefvern
. – RBarryYoung Feb 13 '18 at 22:38