LNNVL to replace NOT IN?

Question

这个有点难搜索，所以我在这里问(我看到关于lnnvl()的问题)。

我有一个查询，看起来像:

SELECT *
FROM foo
WHERE foo.bar NOT IN ('X', 'Y')
OR foo.bar IS NULL;

片刻之前，我了解了lnnvl()，我能够做同样的事情:

SELECT *
FROM foo
WHERE lnnvl(foo.bar = 'X')
AND lnnvl(foo.bar = 'Y');

这非常棒，但在检查更大的集合时不是很可扩展。你们知道有更干净的方法吗?在过去，我做过这样的事情:

SELECT *
FROM foo
WHERE nvl(foo.bar, ' ') NOT IN ('X', 'Y')

我欣赏任何见解!

Answer 1

您的最后一个选项是好的，并导致以下filter谓词

filter(NVL("FOO"."BAR",' ')<>'X' AND NVL("FOO"."BAR",' ')<>'Y')

我看不到其他可能性，因为下面的谓词会导致异常

WHERE lnnvl(foo.bar IN ('X', 'Y')); 
ORA-13207: incorrect use of the [LNNVL] operator

如果你的表非常大，你想避免全表扫描，你甚至可以定义一个基于索引的函数

create index idx on foo(nvl(bar,' ')); 

这将阻止全表扫描，但不幸的是索引不能像往常一样使用-当您检查不平等<>时。

所以结果是一个全索引扫描，也就是说你遍历整个索引，过滤不匹配的键，只访问正确的键。

如果表很大，而结果集很小，这可能是有意义的。

执行计划

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |   250 |   489K|   269   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| FOO  |   250 |   489K|   269   (1)| 00:00:01 |
|*  2 |   INDEX FULL SCAN                   | IDX  |   250 |       |   185   (2)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

2 - filter(NVL("BAR",' ')<>'X' AND NVL("BAR",' ')<>'Y')

率

不能对谓词lnnvl(bar = 'X')使用索引，这将导致明显的全表扫描与谓词filter(LNNVL("BAR"='X'))

无论如何，您可以将谓词重新表述为

WHERE 
case when lnnvl(bar = 'X') then 1 end = 1

和定义FBI索引

create index idx2 on foo( case when lnnvl(bar = 'X') then 1 end ); 

导致niceindex range scan

你甚至可以使用and，例如

where case when lnnvl(bar = 'X') and lnnvl(bar = 'Y') then 1 end = 1