Oracle 11g:查询性能优化
Oracle 11g:查询性能优化
提问于 2017-10-06 08:32:06
对于这个特定的查询，我有性能问题：
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,
REQUIREDDDL
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)
);问题在于RTRIM(XMLAGG(XMLELEMENT(E,WARD_ID,',').EXTRACT('//text()') ORDER BY WARD_ID).GetClobVal(),',') AS wards语句的计算速度非常慢。
我正在寻找的结果是一个名为"wards“的列，所有匹配的I都连接到一个字符串中。
有人有更好的性能解决方案吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2017-10-06 08:55:18
可以将聚合移动到子查询中，这样就不需要按联接表的所有列进行分组：
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此外，如果聚合WARD_ID字符串的长度永远不会超过4000个字符，则可以使用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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/46601578
复制相关文章
相似问题
腾讯云开发者
