SQL query optimisation by replacing subquery

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:

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

Answer

A few points can help you optimize this.

1. Like @Stu pointed out, DISTINCT is unnecessary in IN() clauses.
2. where nvl(matched_alert,'N') != 'Y' isn’t sargeable. It applies a function to a column, which defeats the use of an index.
3. WHERE column = 'constant' exploits an index better than WHERE column <> 'constant'.
4. You can simplify the logic by getting rid of the subquery.
5. 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);