sql - 如何在不等于，NOT IN的情况下重写查询？

Question

我有一个用 NOT IN 和 <> 编写的查询。由于 NOT IN 和 Not equal 运算符，它花费了很多成本。下面是查询

SELECT NVL (SUM (NVL (I.AMOUNT, 0)), 0)
FROM Start_tb C,
  End_tb I
WHERE C.Start_key             = :B3
AND I.End_key                 = C.End_key
AND I.Com_number              <> :B2
AND NVL (I.Process_typ, 'NA') = 'H'
AND NVL (I.Code, 'Elp_01')    = :B1
AND I.Typ_id                NOT IN (3, 4);

以下是解释计划详情

Plan hash value: 34574793
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                         |       |       |  3648 (100)|          |
|   1 |  SORT AGGREGATE                 |                         |     1 |    49 |            |          |
|   2 |   NESTED LOOPS                  |                         |     1 |    49 |  3648   (2)| 00:00:01 |
|   3 |    NESTED LOOPS                 |                         |     1 |    49 |  3648   (2)| 00:00:01 |
|*  4 |     INDEX STORAGE FAST FULL SCAN| Index_clm               |     1 |    13 |  3646   (2)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN            | Index_range             |     1 |       |     1   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS BY INDEX ROWID  | End_tb                  |     1 |    36 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

我可以重写此查询以提高性能吗？

标签： sqloracle

Answer 1

最后一行可以不是这样吗： AND I.type != 3 AND I.type != 4 那么你没有使用“not in”