SQL query optimisation by replacing subquery-CodePudding

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

CodePudding user response：

A few points can help you optimize this.

Like @Stu pointed out, DISTINCT is unnecessary in IN() 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 than WHERE 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);

CodePudding user response：

Reverting that query (with a NOT IN and !=) to its simpler version might look like this:

SELECT COUNT (*)
  FROM frd_rules_time
 WHERE    NVL (matched_alert, 'N') = 'Y'
       OR tc_grp_id NOT LIKE 'N%';

There's no use in trimming result of the count function)

Why? Because this is what your query (with my sample data) returns:

SQL> WITH
  2     frd_rules_time (tc_grp_id, matched_alert)
  3     AS
  4        (SELECT 'ABC', 'Y' FROM DUAL
  5         UNION ALL
  6         SELECT 'NXY', 'N' FROM DUAL
  7         UNION ALL
  8         SELECT 'NBA', NULL FROM DUAL
  9         UNION ALL
 10         SELECT 'NDD', 'Y' FROM DUAL
 11         UNION ALL
 12         SELECT 'CZY', 'N' FROM DUAL)
 13    SELECT *
 14      FROM FRD_RULES_TIME
 15     WHERE tc_grp_id NOT IN (SELECT DISTINCT tc_grp_id
 16                               FROM FRD_RULES_TIME
 17                              WHERE     NVL (matched_alert, 'N') != 'Y'
 18                                    AND tc_grp_id LIKE 'N%')
 19  ORDER BY tc_grp_id;

TC_GRP_ID  MATCHED_ALERT
---------- ---------------
ABC        Y
CZY        N
NDD        Y

SQL>

while "my" query returns the same:

 <snip>
 13    SELECT *
 14      FROM frd_rules_time
 15     WHERE    NVL (matched_alert, 'N') = 'Y'
 16           OR tc_grp_id NOT LIKE 'N%'
 17  ORDER BY tc_grp_id;

TC_GRP_ID  MATCHED_ALERT
---------- ---------------
ABC        Y
CZY        N
NDD        Y

SQL>

CodePudding user response：

Another alternative if you don't have indexes on MATCHED_ALERT or TC_GRP_ID:

SELECT COUNT(TC_GRP_ID)
  FROM FRD_RULES_TIME
 WHERE NVL(MATCHED_ALERT, 'N') = 'Y'
   AND SUBSTR(NVL(TC_GRP_ID, 'X'), 0, 1) <> 'N';