I have the following SQLs executed millions of times a day, a single execution isn't an issue but executing million times is causing some performance issues..Thanks in advance for your help!
NOTE: data can change in any of the tables any time during the day..can't reduce number of executions and this is Oracle 11g env.
SELECT COUNT (*)
INTO v_cnt
FROM t1 cpr, t2 brl
WHERE cpr.cp_id = :1
AND cpr.brl_id = brl.brl_id
AND brl.ranking > 0
AND cpr.security_enabled_ind='Y'
AND cpr.brl_id NOT IN (
SELECT rpr.brl_id
FROM t3 rpr, t4 ppr, t5 plcy
WHERE rpr.ppr_id = ppr.ppr_id
AND ppr.plcy_id = plcy.plcy_id
AND plcy.db_object_name = :2);
IF v_cnt = 0
THEN
SELECT where_clause
FROM
(SELECT where_clause
FROM t4 ppr_o
WHERE ppr_o.ppr_id IN (
SELECT ppr.ppr_id
FROM t1 cpr,
t3 rpr,
t4 ppr,
t5 plcy
WHERE cpr.cp_id = :1
AND cpr.brl_id = rpr.brl_id
AND rpr.ppr_id = ppr.ppr_id
AND ppr.plcy_id = plcy.plcy_id
AND cpr.security_enabled_ind='Y'
AND plcy.db_object_name = :2
AND plcy.statement_type = :3)
ORDER BY NVL (ppr_o.ranking, 0) DESC)
WHERE ROWNUM = 1;
END IF;
Number of records
- t1 -> 205101
- t2 -> 42
- t3 -> 142
- t4 -> 57
- t5 -> 13
Best Answer
I would first consider trying to combine the two queries into one. If the second query could be refactored to return
null
in the case where the first query returned zero would you be able to work with that?For example,
testbed:
refactored query: