How to speed up SELECT query with LISTAGG?

Question

Oracle

Hello.
There are two tables, humans and connects. Like these ones:

-- humans

id              name
1001            Алексеев Алексей Алексеевич
1002            Борисов Борис Борисович
1003            Васильев Василий Васильевич
...


-- connects

id      humanid     type        conn
2001    1001        phone       123456
2002    1001        email       [email protected]
2003    1001        email       [email protected]
2004    1001        email       [email protected]
2005    1002        phone       234532
2006    1002        phone       232323
2007    1002        phone       212121
2008    1003        email       [email protected]
2009    1003        phone       345678
2010    1003        phone       313131

And I want to get this result:

id      name                           phone                    
1001    Алексеев Алексей Алексеевич    123456                  
1002    Борисов Борис Борисович        212121,232323,234532   
1003    Васильев Василий Васильевич    313131,345678

And then the same result with emails.
My request is like this:

with qconnect as
(
    select 
        humanid, 
        listagg(conn, ',') within group(order by conn) as phone
    from connects
    where 
        type = 'phone'
    group by humanid
)
select
    h.id,
    h.name,
    c.phone
from
    humans h,
    qconnect c
where h.id = c.humanid;

The problem is that the tables are actually very large. And, most importantly, if there are tens of thousands of records with phones, then there are tens of millions of records with emails. Therefore, it is not surprising that a request with phones is processed in 5-6 seconds, while a request with emails takes more than five minutes.

Actually a question.
Is it possible to speed up this request? For some reason, I think that the root of evil is hidden in listagg, somehow it is not written that way. Somehow you can get through to h.id from there. But how?
Thank you!

Answer 1

Try like this and it is desirable to see the query plan

select h.id, 
       h.name, 
       listagg(c.conn, ',') within group(order by c.conn) as phone 
  from humans h
       left join connects c on c.humanid = h.id
 where c.type = 'phone'
 group by h.id, 
          h.name