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