Hi all,
We have resource intensive query which causes high concurrency in the system.Application vendor provided the solution in Postgres SQL syntax as their cloud system is based on Postgres.
Below is the problem causing query.
SELECT cc.pk1, cc.pk1 AS course_contents_pk1, ccr.review_date, content_is_available( cc.pk1, :1 , cc.available_ind, cc.partially_visible_ind, cc.cnthndlr_handle, cc.web_url_host, cc.start_date, cc.end_date, sysdate, ar.enabled ) AS PassesRule, CASE WHEN cc.is_group_content = 'N' THEN 'Y' WHEN ccg.assigned_ind = 'Y' AND gu.groups_pk1 IS NOT NULL THEN 'Y' ELSE 'N' END AvailGroup FROM course_contents cc CROSS JOIN (SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END AS enabled FROM avl_enabled_course_vw WHERE COURSE_PK1=:2 ) ar LEFT JOIN course_users cu ON cu.crsmain_pk1 = cc.crsmain_pk1 AND cu.users_pk1 = :3 AND cu.row_status = 0 LEFT JOIN course_contents_reviewed ccr ON cu.users_pk1 = ccr.users_pk1 AND cc.pk1 = ccr.course_contents_pk1 LEFT JOIN group_users gu ON gu.course_users_pk1 = cu.pk1 LEFT JOIN groups g ON gu.groups_pk1 = g.pk1 AND g.available_ind = 'Y' LEFT JOIN course_content_group ccg ON ccg.course_contents_pk1 = cc.pk1 AND g.pk1 = ccg.groups_pk1 WHERE cc.crsmain_pk1 = :4
Solution provided by vendor is below.
This has been reviewed before, and the suggested solution was to create two additional indexes
CREATE INDEX CONCURRENTLY groups_bmh1 ON groups(pk1) WHERE available_ind = 'Y';
CREATE INDEX CONCURRENTLY course_users_bmh1 ON course_users(crsmain_pk1,users_pk1) WHERE row_status = 0;
(Note this is Postgres syntax, your will need to translate it to Oracle)
Can someone helps to translate above to Oracle