数据库版本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'))