Oracle 11g: query performance optimization

Question

I have performance problem with this specific query:

SELECT ID,
       NAME,
       CREATION_DATE,
       MODIFICATION_DATE,
       CREATION_USER,
       MODIFICATION_USER,
       CODE,
       START_DATE,
       TO_CHAR(ANSWER) AS ANSWER,
       STATUS,
       RESUME,
       REQUIRED,
       RTRIM(
         XMLAGG(
           XMLELEMENT(E,QW.WARD_ID,',').EXTRACT('//text()')
           ORDER BY QW.WARD_ID
         ).GetClobVal(),
         ','
       ) AS wards
FROM   RD_QUESTIONS Q
       LEFT JOIN RD_QUESTIONS_WARDS QW ON QW.QUESTION_ID = ID
GROUP BY ID,
       NAME,
       CREATION_DATE,
       MODIFICATION_DATE,
       CREATION_USER,
       MODIFICATION_USER,
       CODE,
       START_DATE,
       TO_CHAR(ANSWER),
       STATUS,
       RESUME,
       REQUIRED

DDL

CREATE TABLE RD_QUESTIONS(
    ID NUMBER(38, 0),
    NAME VARCHAR(255) NOT NULL,
    CREATION_DATE TIMESTAMP(6),
    MODIFICATION_DATE TIMESTAMP(6),
    CREATION_USER VARCHAR(20),
    MODIFICATION_USER VARCHAR(20),
    SECTION_ID NUMBER(38, 0),
    CHAPTER_ID NUMBER(38, 0),

    CONSTRAINT RD_QUESTIONS_PK3 PRIMARY KEY (ID),
);

CREATE TABLE RD_QUESTIONS_WARDS(
    QUESTION_ID NUMBER(38, 0),
    WARD_ID NUMBER(38, 0),

    CONSTRAINT RD_QUESTIONS_WARDS_PK4 PRIMARY KEY (QUESTION_ID, WARD_ID),
    CONSTRAINT RD_QUESTIONS_FK4 FOREIGN KEY (QUESTION_ID) REFERENCES RD_QUESTIONS(ID)
);

the problem is with the statement RTRIM(XMLAGG(XMLELEMENT(E,WARD_ID,',').EXTRACT('//text()') ORDER BY WARD_ID).GetClobVal(),',') AS wards that is really slow to compute.

The result I'm looking for, is a column named "wards" with all matching ids joined to a string.

Anyone with a better performance solution?

Answer 1

You can move the aggregation into a sub-query so you do not need to group by all the columns of the joined table:

SELECT Q.ID,
       Q.NAME,
       Q.CREATION_DATE,
       Q.MODIFICATION_DATE,
       Q.CREATION_USER,
       Q.MODIFICATION_USER,
       Q.CODE,
       Q.START_DATE,
       TO_CHAR(Q.ANSWER) AS ANSWER,
       Q.STATUS,
       Q.RESUME,
       Q.REQUIRED,
       QW.wards
FROM   RD_QUESTIONS Q
       LEFT JOIN (
         SELECT Question_ID,
                RTRIM(
                  XMLAGG(
                    XMLELEMENT(E,WARD_ID,',').EXTRACT('//text()')
                    ORDER BY WARD_ID
                 ).GetClobVal(),
                 ','
                ) AS wards
         FROM   RD_QUESTIONS_WARDS
         GROUP BY QUESTION_ID
       ) QW
       ON QW.QUESTION_ID = Q.ID

Also, if the length of the aggregated WARD_ID string is never going to be more than 4000 characters you could use LISTAGG:

FROM   RD_QUESTIONS Q
       LEFT JOIN (
         SELECT Question_ID,
                LISTAGG( WARD_ID, ',' ) WITHIN GROUP ( ORDER BY WARD_ID ) AS wards
         FROM   RD_QUESTIONS_WARDS
         GROUP BY QUESTION_ID
       ) QW
       ON QW.QUESTION_ID = Q.ID