The following query works and gives me the expected result but i’d like to optimise it. Is there a way to avoid the subquery, using only the conditions in the main query to arrive at the same result:
x
select trim(count(tc_grp_id))
from FRD_RULES_TIME
where tc_grp_id NOT IN
(SELECT DISTINCT tc_grp_id
from FRD_RULES_TIME
where nvl(matched_alert,'N') != 'Y'
and tc_grp_id like 'N%');
PS: In PLSQL, NVL function lets us substitute a value when a null value is encountered
Thanks for any input
Advertisement
Answer
A few points can help you optimize this.
- Like @Stu pointed out,
DISTINCT
is unnecessary inIN()
clauses. where nvl(matched_alert,'N') != 'Y'
isn’t sargeable. It applies a function to a column, which defeats the use of an index.WHERE column = 'constant'
exploits an index better thanWHERE column <> 'constant'
.- You can simplify the logic by getting rid of the subquery.
TRIM(number)
is not useful unless you must output the number as a text string.
Applying those principles the query is.
select count(tc_grp_id)
from FRD_RULES_TIME
where (matched_alert IS NULL OR matched_alert = 'N')
and tc_grp_id LIKE 'N%';
If it’s still too slow, you can create a covering index to accelerate it.
CREATE INDEX match_grp ON FRD_RULES_TIME (matched_alert, tc_grp_id);