1

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.

  • Sounds like you need an index on that table. – RBarryYoung Feb 13 '18 at 22:21
  • 1
    what's the query plan look like? – DForck42 Feb 13 '18 at 22:24
  • @RBarryYoung I have an index on the table (for the subquery) but the leading column is not Vernum could that be the issue – Batman Feb 13 '18 at 22:31
  • @DForck42 looks like it's using the indices but the cost is massive imgur.com/a/IHWA5 – Batman Feb 13 '18 at 22:37
  • Try putting an index just on vernum and another just on defvern. – RBarryYoung Feb 13 '18 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.