Patch to Optimize To Do Summary Query to Improve Performance (Doc ID 2108486.1)

Last updated on MAY 09, 2024

Applies to:

Symptoms

ToDo Summary query takes a long time and there is a <> operator observed in the query. 

SELECT
 DISTINCT MIN (tde.TD_PRIORITY_FLG) AS tdePrty, tl.DESCR, tl.TD_TYPE_CD
FROM CI_TD_ENTRY tde,
  CI_TD_TYPE_L tl,
  CI_ROLE_USER ru,
  CI_TD_VAL_ROLE r
WHERE ru.USER_ID = 'ZZZ'
  AND ru.ROLE_ID = r.ROLE_ID
  AND r.TD_TYPE_CD = tl.TD_TYPE_CD
  AND tl.LANGUAGE_CD = 'ENG'
  AND tde.ROLE_ID = r.ROLE_ID
  AND tde.TD_TYPE_CD = tl.TD_TYPE_CD
  AND tde.ENTRY_STATUS_FLG <> 'C'
GROUP BY tl.DESCR, tl.TD_TYPE_CD
ORDER BY tdePrty, tl.DESCR;

Performance improvement was shown with the following changes:

SELECT
 DISTINCT MIN (tde.TD_PRIORITY_FLG) AS tdePrty, tl.DESCR, tl.TD_TYPE_CD
FROM CI_TD_ENTRY tde,
  CI_TD_TYPE_L tl,
  CI_ROLE_USER ru,
  CI_TD_VAL_ROLE r
WHERE ru.USER_ID = 'ZZZ'
  AND ru.ROLE_ID = r.ROLE_ID
  AND r.TD_TYPE_CD = tl.TD_TYPE_CD
  AND tl.LANGUAGE_CD = 'ENG'
  AND tde.ROLE_ID = r.ROLE_ID
  AND tde.TD_TYPE_CD = tl.TD_TYPE_CD
  AND tde.ENTRY_STATUS_FLG IN ('W','O')
GROUP BY tl.DESCR, tl.TD_TYPE_CD
ORDER BY tdePrty, tl.DESCR;

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.