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?

Please can you give the DDL for the two tables and edit your query to show which table the various columns belong to as that will make a huge difference to your options. I.e. Is it Q.WARD_ID or QW.WARD_ID (and similar for the other columns) — MT0
– MT0, Commented Oct 6, 2017 at 8:50

MT0 · Accepted Answer · 2017-10-06 08:56:37Z

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

Collectives™ on Stack Overflow

Oracle 11g: query performance optimization

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related