oracle or的等价改写

数据库版本11g

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

创建测试表

create table t1 as select * from dba_objects;

不等价改写1：or改写为union

SQL> select owner from t1 where object_id=12 or object_id=16;

OWNER
------------------------------
SYS
SYS

SQL> select owner from t1 where object_id=12 
  union 
select owner from t1 where object_id=16;

OWNER
------------------------------
SYS                                                                  --（union多了去重，两条数据去重后查询出一条）

不等价改写2：or改写为union all

SQL> select owner from t1 where object_id=16 or object_name='TS$';   --（此处object_id=16对应的object_name就是TS$）

OWNER
------------------------------
SYS

SQL> select owner from t1 where object_id=16 union all select owner from t1 where object_name='TS$';

OWNER
------------------------------
SYS
SYS

等价改写（union all+lnnvl）

select owner from t1 where object_id=12 
  union all 
select owner from t1 where object_id=16 and lnnvl(object_id=12);


select owner from t1 where object_id=16
  union all
select owner from t1 where object_name='TS$' and lnnvl(object_id=16);

在执行计划的谓词信息部分也可以看到lnnvl这个函数

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

   3 - access("OBJECT_NAME"='TEST')
   4 - filter(LNNVL("OBJECT_NAME"='TEST'))