SQL优化 where条件单列is null优化改写

1.创建测试表及测试数据

create table t65 as select * from dba_objects;
update t65 set owner=null  where rownum<10;
commit;
exec dbms_stats.gather_table_stats('sys','t65',cascade => true ,degree => 2,no_invalidate => false);

2.测试全表扫描逻辑读1237

3.创建普通索引测试，无法使用到索引，索引中不存放空值。

create index idx_t65_01 on t65(owner) online;

4.改写where语句is null为nvl ，执行结果一致

select object_name from t65 where OWNER is null;
select object_name from t65 where nvl(OWNER,'0')='0';

5.创建nvl 函数索引，逻辑读降低为7