Optimization of MySQL for not in and minus usage

Before optimization:

select count(t.id)
 from test t
 where t.status = 1
  and t.id not in (select distinct a.app_id
           from test2 a
           where a.type = 1
            and a.rule_id in (152, 153, 154))
      
 17:20:57 laojiu>@plan

PLAN_TABLE_OUTPUT
 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
Plan hash value: 684502086

 -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -
| Id | Operation      | Name       | Rows | Bytes | Cost (%CPU)| Time   |
 -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -
|  0 | SELECT STATEMENT  |         |   1 |  18 |  176K (2)| 00:35:23 |
|  1 | SORT AGGREGATE   |         |   1 |  18 |      |     |
|* 2 |  FILTER      |         |    |    |      |     |
|* 3 |  TABLE ACCESS FULL| test   | 1141 | 20538 |  845  (2)| 00:00:11 |
|* 4 |  TABLE ACCESS FULL| test2 |   1 |  12 |  309  (2)| 00:00:04 |
 -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -

Predicate Information (identified by operation id):
 -- -- -- -- -- -- -- -- - 

  2  �  filter( NOT EXISTS (SELECT /*+ */ 0 FROM  " test2 "   " A "  WHERE
        " A " . " type " =1 AND ( " A " . " RULE_ID " =152 OR  " A " . " RULE_ID " =153 OR
        " A " . " RULE_ID " =154) AND LNNVL( " A " . " APP_ID " <>:B1)))
  3  �  filter( " T " . " status " =1)
  4  �  filter( " A " . " type " =1 AND ( " A " . " RULE_ID " =152 OR  " A " . " RULE_ID " =153 OR
        " A " . " RULE_ID " =154) AND LNNVL( " A " . " APP_ID " <>:B1))
Statistics
 -- -- -- -- -- -- -- -- -- - -
     0 recursive calls
     0 db block gets
  1762169 consistent gets
     0 physical reads
     0 redo size
    519 bytes sent via SQL*Net to client
    492 bytes received via SQL*Net from client
     2 SQL*Net roundtrips to/from client
     0 sorts (memory)
     0 sorts (disk)
     1 rows processed
21 rows selected.

After the optimization:

 select count(*) from(
 select t.id
  from test t
 where t.status = 1
 minus
 select distinct a.app_id
  from test2 a
 where a.type = 1
  and a.rule_id in (152, 153, 154))
17:23:33 laojiu>@plan

PLAN_TABLE_OUTPUT
 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
Plan hash value: 631655686

 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  �  
| Id | Operation       | Name       | Rows | Bytes |TempSpc| Cost (%CPU)| Time   |
 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  �  
|  0 | SELECT STATEMENT   |         |   1 |    |    | 1501  (2)| 00:00:19 |
|  1 | SORT AGGREGATE    |         |   1 |    |    |      |     |
|  2 |  VIEW        |         | 1141 |    |    | 1501  (2)| 00:00:19 |
|  3 |  MINUS       |         |    |    |    |      |     |
|  4 |   SORT UNIQUE    |         | 1141 | 20538 |    |  846  (2)| 00:00:11 |
|* 5 |   TABLE ACCESS FULL| test   | 1141 | 20538 |    |  845  (2)| 00:00:11 |
|  6 |   SORT UNIQUE    |         | 69527 |  814K| 3632K|  654  (2)| 00:00:08 |
|* 7 |   TABLE ACCESS FULL| test2 | 84140 |  986K|    |  308  (2)| 00:00:04 |
 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  �  

Predicate Information (identified by operation id):
 -- -- -- -- -- -- -- -- - 

  5  �  filter( " T " . " status " =1)
  7  �  filter( " A " . " type " =1 AND ( " A " . " RULE_ID " =152 OR  " A " . " RULE_ID " =153 OR
        " A " . " RULE_ID " =154))

21 rows selected.
Statistics
 -- -- -- -- -- -- -- -- -- - -
     1 recursive calls
     0 db block gets
    2240 consistent gets
     0 physical reads
     0 redo size
    516 bytes sent via SQL*Net to client
    492 bytes received via SQL*Net from client
     2 SQL*Net roundtrips to/from client
     2 sorts (memory)
     0 sorts (disk)
     1 rows processed

When optimizing sql, we need to change 1 train of thought and rewrite sql equivalently.

The rewritten sql was drastically changed due to the logic reading, and the results were soon available.

Article 1 There is one function in the sql execution plan, LNNVL(" A ". "APP_ID") < > : B1), lnnvl (exp)

If the result of exp is false or unknown, lnnvl returns true;

If exp results in true, return false.