Oracle LISTAGG causing query to run S L O W

[WolfShade]

Hello, all,

I've got a query that is giving me fits.

I'm trying to bring some code that my boss recently wrote into the 21st century.  One section that he wrote, he is using three queries to get base information, looping through one of the queries and running a query within that loop.  I hate this.  Every time I see code that runs a query within a loop, it makes my teeth itch.  So, I'm trying to run a single query using LEFT OUTER JOIN.

I've got it working, but for one thing.  I am trying to get our Oracle db to aggregate some data into a string using LISTAGG(), and that is causing this query to take 14 seconds to get the data (the way my boss wrote the code, it's almost instant.)  Of the two tables involved, one has ~2000 records, the other ~4000, and the query is retrieving 23.  If I take out the LISTAGG() portion, the query is almost instant.

I was hoping to get the db to do more of the heavy lifting.  Is there anything faster than LISTAGG() for aggregating data into a string in Oracle 12c?

SELECT m1.uuid, m1.title, m1.description, 
     (SELECT listagg(title,', ') within group (order by null) csv 
      FROM main 
      WHERE uuid in (SELECT role_uuid FROM assoc WHERE task_uuid = m1.uuid)) related_titles
FROM main m1
WHERE m1.uuid in (SELECT a1.task_uuid FROM assoc a1 WHERE a1.role_uuid = '{uuid}' AND task_uuid is not null)
ORDER BY m1.title

V/r,

^ _ ^

[BKBK]

Does it help to do a GROUP BY m1.title as well?

[WolfShade]

I'll give that a shot and report back.  Thanks!

V/r,

^ _ ^

[WolfShade]

Even though "related_titles" is an aggregation, apparently you can't GROUP BY an alias.  And if I try to use anything else (uuid, title, description), it just errors.  Even if I use all four columns, it errors.

If I apply the GROUP BY to the sub-query, it tells me (even though it's an aggregate) that the sub-query is returning more than one row.

V/r,

^ _ ^

[BKBK]

Could something like this work

SELECT uuid, title, description,  

     (SELECT listagg(title,', ') within group (order by null) csv  

      FROM main  

      WHERE uuid in (SELECT role_uuid FROM assoc WHERE task_uuid = m1.uuid)) related_titles 

FROM main m1 

WHERE uuid in (SELECT a1.task_uuid FROM assoc a1 WHERE a1.role_uuid = '{uuid}' AND task_uuid is not null) 

GROUP BY title 

ORDER BY title

[WolfShade]

That code looks like you removed the table alias prepend from the SELECT statement (uuid, title, description), but kept the table aliased (from main m1).  Or am I missing something?

If I alias the table, but don't use the alias on the column names, it will error.

And it's always been my experience that if I GROUP BY a column that is not a function or aggregate, then Oracle complains that the column isn't a GROUP BY column.

V/r,

^ _ ^

[WolfShade]

I hate to say it, but I've spent too much time focusing on this one issue.  I am sad to report that I have been forced (my decision) to give up the ghost on this and relent to having my teeth itch by using a small query (returns a string, not a query) inside the CFOUTPUT loop of the primary query.  I can't keep banging my head on this brick wall, anymore.

It is running quickly, no matter how much I dislike the code.

BKBK​, thanks for your attention to this.

V/r,

^ _ ^

[BKBK]

I can see your point. It seems that, for every row, the listagg subquery runs across every uuid value in main. You will then have a time-consuming cross-product.

Reverting to the original code design will of course save you time. For now. But I share your feeling that the query needs to be improved.

If you wish to revisit the problem in future, I will suggest finding a solution that involves just one Where clause. You'll probably have to redesign. The reason being that, while the main query filters by task, the listAgg subquery filters by role.

That immediately suggests a possible design solution. Add a UUIDType column to the main table. Its entries will be, for example, 'role' and 'task'.  You will then be able to do something like

SELECT uuid, title, description, listagg(title,', ') within group (order by title) csv
FROM main

WHERE uuid in (WHERE-clause based on UUIDTypes 'task', 'role', and so on) 

GROUP BY title  

ORDER BY title 

[WolfShade]

There is an associative table that contains role, task, knowledge, and abilities.  Roles have tasks, tasks have knowledge, and knowledge have abilities.  (My boss designed it.)  I'll just let it be.  He plans on retiring, soon, so when that happens I will be the architect. 

V/r,

^ _ ^

[WolfShade]

Okay.. I just made an important discovery.  It's not the LISTAGG(), after all.  It's the sub-query of the sub-query:

(SELECT role_uuid FROM assoc WHERE task_uuid = m1.uuid)

Apparently, this is what's slowing things down.  Not sure where to go from here.  It's looking like I may not be able to do this in one query (not at the expense of 14-30 seconds.)

Any thoughts?

V/r,

^ _ ^