Home » SQL & PL/SQL » SQL & PL/SQL » Please help me tune the query (merged) (Oracle 19c)
| Please help me tune the query (merged) [message #690494] |
Thu, 09 July 2026 03:33  |
 |
learner123
Messages: 7
Registered: January 2021
|
Junior Member |
|
|
WITH
LINKED_INCDNT_FER AS
(
SELECT RL.*,
(SELECT NVL(H.REVIEWED, 0)
FROM ALM_OWNER.ALM_LINKED_INCDNT_FER H
WHERE H.LINKED_INCDNT_ID = RL.LINKED_INCDNT_ID) AS reviewed,
(SELECT NVL(H.IS_LINKED, 0)
FROM ALM_OWNER.ALM_LINKED_INCDNT_FER H
WHERE H.LINKED_INCDNT_ID = RL.LINKED_INCDNT_ID) AS isLinked,
(SELECT LISTAGG(DISTINCT G.REASON_LINKED, '; ')
WITHIN GROUP (ORDER BY G.REASON_LINKED)
FROM ALM_OWNER.ALM_REASON_LINKED G
WHERE G.LINKED_INCDNT_ID = RL.LINKED_INCDNT_ID
GROUP BY G.LINKED_INCDNT_ID) AS reasonLinked,
C.INCDNT_NUMBER AS CUR_INCDNT_NUMBER,
C.INCDNT_SYSTEM AS CUR_INCDNT_SYSTEM,
C.PROCESS_ID AS CUR_PROCESS_ID,
(SELECT MIN(START_DATE)
FROM ALM_OWNER.PROCESS_LOG A
WHERE A.OBJECT_ID = C.INCDNT_ID
AND UPPER(PROCESS_STATE) = 'INCDNT UNDER REVIEW'
GROUP BY OBJECT_ID) AS REVIEW_DT
FROM ALM_OWNER.ALM_LINKED_INCDNT RL
INNER JOIN ALM_OWNER.ALM_INCDNT C
ON NVL(C.INCDNT_TYPE,'X') <> 'IEI'
AND RL.INCDNT_ID = C.INCDNT_ID
AND C.INCDNT_ID = NVL(P_INCDNT_ID,C.INCDNT_ID)
WHERE RL.MANUAL_FLAG = 0
AND RL.INCDNT_ID = NVL(P_INCDNT_ID,RL.INCDNT_ID)
),
LINKED_INCDNT AS
(
SELECT DISTINCT
RL.INCDNT_ID,
RL.INCDNT_NUMBER,
CS.PROCESS_ID,
CS.IS_COVERT,
CS.IS_RESTRICTED,
CS.INCDNT_OPEN_DATE AS OPENED_DATE,
CS.INCDNT_CLOSED_DATE AS CLOSED_DATE,
MCA.IS_REPORTABLE,
(SELECT TEAM_CATEGORY_CD
FROM ALM_OWNER.ALM_TEAM T
WHERE T.TEAM_ID = CS.TEAM_ID) AS TEAM_CATEGORY_CD,
(SELECT TEAM_NAME
FROM ALM_OWNER.ALM_TEAM T
WHERE T.TEAM_ID = CS.TEAM_ID) AS TEAM_NAME,
(SELECT PROCESS_STATE
FROM ALM_OWNER.PROCESS W
WHERE W.PROCESS_ID = CS.PROCESS_ID) AS incdntStatus,
(SELECT EL.LAST_NAME || ', ' || EL.FIRST_NAME
FROM ALM_OWNER.USER_PROFILE EL
WHERE EL.USER_ID = CS.ANALYST_ID) AS investigator,
CS.INCDNT_NAME
FROM LINKED_INCDNT_FER RL
INNER JOIN ALM_OWNER.ALM_INCDNT CS
ON NVL(CS.INCDNT_TYPE,'X') <> 'IEI'
AND CS.PROCESS_ID <> 4
AND RL.INCDNT_NUMBER = CS.INCDNT_NUMBER
AND RL.INCDNT_ID = NVL(P_INCDNT_ID,RL.INCDNT_ID)
INNER JOIN ALM_OWNER.ALM_INCDNT_ATTRIBUTES MCA
ON CS.INCDNT_ID = MCA.INCDNT_ID
)
SELECT
A.LINKED_INCDNT_ID AS linkedIncdntId,
A.INCDNT_ID AS incdntId,
A.INCDNT_NUMBER AS incdntNumber,
B.INCDNTSTATUS AS incdntStatus,
B.TEAM_NAME AS team,
A.CREATED_BY AS createdBy,
B.investigator AS investigator,
CASE
WHEN B.PROCESS_ID = 4
THEN
CASE
WHEN B.IS_REPORTABLE = 1 THEN 'Yes'
WHEN B.IS_REPORTABLE = 0 THEN 'No'
ELSE 'Pending'
END
WHEN (B.INCDNT_NUMBER IS NOT NULL
AND A.CUR_PROCESS_ID IN (1,12,15,16))
THEN
CASE
WHEN B.IS_REPORTABLE = 1 THEN 'Yes'
WHEN B.IS_REPORTABLE = 0 THEN 'No'
ELSE 'Pending'
END
WHEN (B.INCDNT_NUMBER IS NOT NULL
AND SUBSTR(A.CUR_INCDNT_NUMBER,0,3) IN ('ALM','FRQ')
AND A.CUR_PROCESS_ID NOT IN (1,12,15,16))
THEN
CASE
WHEN B.IS_REPORTABLE = 1
AND A.REVIEW_DT >= B.CLOSED_DATE
THEN 'Yes'
WHEN B.IS_REPORTABLE = 0
AND A.REVIEW_DT >= B.CLOSED_DATE
THEN 'No'
ELSE 'Pending'
END
ELSE
DECODE(
A.SUSP_FILED_FLAG,
1,'Yes',
0,'No',
2,'Undetermined',
''
)
END AS suspFiledFlag,
A.reasonLinked AS reasonLinked,
A.INCDNT_DESCRIPTION AS incdntDescription,
A.INCDNT_SYSTEM AS incdntSystem,
A.CREATED_DATE AS createdDate,
A.MANUAL_FLAG AS manualFlag,
A.reviewed AS reviewed,
NVL(A.REVIEW_REQUIRED,0) AS reviewRequired,
A.isLinked AS isLinked,
B.OPENED_DATE AS openedDate,
B.CLOSED_DATE AS closedDate,
B.IS_COVERT AS isCovert,
B.IS_RESTRICTED AS isRestricted,
1 AS isAlmIncdnt,
B.TEAM_CATEGORY_CD AS teamCategoryCode,
B.INCDNT_NAME AS INCDNT_NAME
FROM LINKED_INCDNT_FER A
INNER JOIN LINKED_INCDNT B
ON A.INCDNT_ID = B.INCDNT_ID
AND A.INCDNT_NUMBER = B.INCDNT_NUMBER;
[Updated on: Thu, 09 July 2026 03:39] Report message to a moderator |
|
|
|
| Please tune the query [message #690495 is a reply to message #690494] |
Thu, 09 July 2026 03:46  |
|
learner123
Messages: 7
Registered: January 2021
|
Junior Member |
|
|
[quote]
[code]WITH
LINKED_INCDNT_FER AS
(
SELECT RL.*,
(SELECT NVL(H.REVIEWED, 0)
FROM ALM_OWNER.ALM_LINKED_INCDNT_FER H
WHERE H.LINKED_INCDNT_ID = RL.LINKED_INCDNT_ID) AS reviewed,
(SELECT NVL(H.IS_LINKED, 0)
FROM ALM_OWNER.ALM_LINKED_INCDNT_FER H
WHERE H.LINKED_INCDNT_ID = RL.LINKED_INCDNT_ID) AS isLinked,
(SELECT LISTAGG(DISTINCT G.REASON_LINKED, '; ')
WITHIN GROUP (ORDER BY G.REASON_LINKED)
FROM ALM_OWNER.ALM_REASON_LINKED G
WHERE G.LINKED_INCDNT_ID = RL.LINKED_INCDNT_ID
GROUP BY G.LINKED_INCDNT_ID) AS reasonLinked,
C.INCDNT_NUMBER AS CUR_INCDNT_NUMBER,
C.INCDNT_SYSTEM AS CUR_INCDNT_SYSTEM,
C.PROCESS_ID AS CUR_PROCESS_ID,
(SELECT MIN(START_DATE)
FROM ALM_OWNER.PROCESS_LOG A
WHERE A.OBJECT_ID = C.INCDNT_ID
AND UPPER(PROCESS_STATE) = 'INCDNT UNDER REVIEW'
GROUP BY OBJECT_ID) AS REVIEW_DT
FROM ALM_OWNER.ALM_LINKED_INCDNT RL
INNER JOIN ALM_OWNER.ALM_INCDNT C
ON NVL(C.INCDNT_TYPE,'X') <> 'IEI'
AND RL.INCDNT_ID = C.INCDNT_ID
AND C.INCDNT_ID = NVL(P_INCDNT_ID,C.INCDNT_ID)
WHERE RL.MANUAL_FLAG = 0
AND RL.INCDNT_ID = NVL(P_INCDNT_ID,RL.INCDNT_ID)
),
LINKED_INCDNT AS
(
SELECT DISTINCT
RL.INCDNT_ID,
RL.INCDNT_NUMBER,
CS.PROCESS_ID,
CS.IS_COVERT,
CS.IS_RESTRICTED,
CS.INCDNT_OPEN_DATE AS OPENED_DATE,
CS.INCDNT_CLOSED_DATE AS CLOSED_DATE,
MCA.IS_REPORTABLE,
(SELECT TEAM_CATEGORY_CD
FROM ALM_OWNER.ALM_TEAM T
WHERE T.TEAM_ID = CS.TEAM_ID) AS TEAM_CATEGORY_CD,
(SELECT TEAM_NAME
FROM ALM_OWNER.ALM_TEAM T
WHERE T.TEAM_ID = CS.TEAM_ID) AS TEAM_NAME,
(SELECT PROCESS_STATE
FROM ALM_OWNER.PROCESS W
WHERE W.PROCESS_ID = CS.PROCESS_ID) AS incdntStatus,
(SELECT EL.LAST_NAME || ', ' || EL.FIRST_NAME
FROM ALM_OWNER.USER_PROFILE EL
WHERE EL.USER_ID = CS.ANALYST_ID) AS investigator,
CS.INCDNT_NAME
FROM LINKED_INCDNT_FER RL
INNER JOIN ALM_OWNER.ALM_INCDNT CS
ON NVL(CS.INCDNT_TYPE,'X') <> 'IEI'
AND CS.PROCESS_ID <> 4
AND RL.INCDNT_NUMBER = CS.INCDNT_NUMBER
AND RL.INCDNT_ID = NVL(P_INCDNT_ID,RL.INCDNT_ID)
INNER JOIN ALM_OWNER.ALM_INCDNT_ATTRIBUTES MCA
ON CS.INCDNT_ID = MCA.INCDNT_ID
)
SELECT
A.LINKED_INCDNT_ID AS linkedIncdntId,
A.INCDNT_ID AS incdntId,
A.INCDNT_NUMBER AS incdntNumber,
B.INCDNTSTATUS AS incdntStatus,
B.TEAM_NAME AS team,
A.CREATED_BY AS createdBy,
B.investigator AS investigator,
CASE
WHEN B.PROCESS_ID = 4
THEN
CASE
WHEN B.IS_REPORTABLE = 1 THEN 'Yes'
WHEN B.IS_REPORTABLE = 0 THEN 'No'
ELSE 'Pending'
END
WHEN (B.INCDNT_NUMBER IS NOT NULL
AND A.CUR_PROCESS_ID IN (1,12,15,16))
THEN
CASE
WHEN B.IS_REPORTABLE = 1 THEN 'Yes'
WHEN B.IS_REPORTABLE = 0 THEN 'No'
ELSE 'Pending'
END
WHEN (B.INCDNT_NUMBER IS NOT NULL
AND SUBSTR(A.CUR_INCDNT_NUMBER,0,3) IN ('ALM','FRQ')
AND A.CUR_PROCESS_ID NOT IN (1,12,15,16))
THEN
CASE
WHEN B.IS_REPORTABLE = 1
AND A.REVIEW_DT >= B.CLOSED_DATE
THEN 'Yes'
WHEN B.IS_REPORTABLE = 0
AND A.REVIEW_DT >= B.CLOSED_DATE
THEN 'No'
ELSE 'Pending'
END
ELSE
DECODE(
A.SUSP_FILED_FLAG,
1,'Yes',
0,'No',
2,'Undetermined',
''
)
END AS suspFiledFlag,
A.reasonLinked AS reasonLinked,
A.INCDNT_DESCRIPTION AS incdntDescription,
A.INCDNT_SYSTEM AS incdntSystem,
A.CREATED_DATE AS createdDate,
A.MANUAL_FLAG AS manualFlag,
A.reviewed AS reviewed,
NVL(A.REVIEW_REQUIRED,0) AS reviewRequired,
A.isLinked AS isLinked,
B.OPENED_DATE AS openedDate,
B.CLOSED_DATE AS closedDate,
B.IS_COVERT AS isCovert,
B.IS_RESTRICTED AS isRestricted,
1 AS isAlmIncdnt,
B.TEAM_CATEGORY_CD AS teamCategoryCode,
B.INCDNT_NAME AS INCDNT_NAME
FROM LINKED_INCDNT_FER A
INNER JOIN LINKED_INCDNT B
ON A.INCDNT_ID = B.INCDNT_ID
AND A.INCDNT_NUMBER = B.INCDNT_NUMBER;
[/code]
[/quote] |
|
|
|
| Re: Please tune the query [message #690496 is a reply to message #690495] |
Thu, 09 July 2026 05:16 |
John Watson
Messages: 9003
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Without any information about the tables or an execution plan or what performance you have and require, one cannot really advise. However, I can see two things that are known to cause problems in many queries.
First, both your CTEs are projecting scalar subqueries. Oracle will often have to run them as correlated subqueries leading to the possibility of repeated executions. In some case the optimizer can re-write a projected subquery as an outer join, but I think that unlikely in your case. You need to re-write them as outer joins yourself.
Second, several of your predicates use columns wrapped in functions. This will likely cripple the optimizer because it will have no idea of how many rows will be returned and will suppress index usage. You need to get rid of those functions.
If you capture an execution plan, with the execution statistics, things will become clearer.
|
|
|
|
Goto Forum:
Current Time: Wed Jul 15 02:56:09 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
