Oracle Index 和null 研究

Indexing null values安装关系数据库理论, null表示未知，Oracle b-tree index是不包含null的。考虑如下表：create table tt （t_id number, t_name varchar2（10））;
create index tt_idx on tt（t_name）;select * from tt where t_name is null是不会使用index scan的，这经常会造成性能问题。解决办法就是：创建一个函数索引，并在select 中包含该函数，如：create index tt_idex on tt（ nvl（t_name）, 1）;
select * from tt where nvl（t_name,1）=1;从11g开始有另一个方法：create index tt_idx on tt（t_name asc, 1）;这里1可以是任意数字或字母。而这时，select语句的谓词不需要更改，还是t_name is null.

Uniqueness and nulldrop index tt_idx;
create unique index tt_idx on tt（t_name）;
insert into tt values（1, null）;
commit;
insert into tt values（1, null）;
commit;这段SQL可以执行成功。这是因为null不被索引包含。create table ttt2 （tt2_id number, tt21 varchar2（10）, tt22 varchar2（10））;
create unique index ttt2_idx on ttt2（tt21, tt22）;
--Successful
insert into ttt2 values（1, null, null）;
insert into ttt2 values（1, null, null）;
commit;
--Fail
insert into ttt2 values（1, "1", null）;
insert into ttt2 values（1, "1", null）;
commit;第二个事务会失败。因为null不被索引包含，两个"1"就是重复值！ Conditional uniqueness 假如有需求：

tt21, tt22都可以为null
仅在tt21和tt22都不为null时，需要保证唯一性！

这时的解决方法：Function based index 。create table ttt2 （tt2_id number, tt21 varchar2（10）, tt22 varchar2（10））;
create or replace function conditional_uniqueness（p_tt21 varchar2, p_tt22 varchar2）
return varchar2
DETERMINISTIC
as
begin
if（p_tt21 is not null and p_tt22 is not null） then
return p_tt21||p_tt22;
else
return null;
end if;
end;
create unique index ttt2_idx on ttt2（conditional_uniqueness（tt21, tt22））;
--Fail！
insert into ttt2 values（1, "1","1"）;
insert into ttt2 values（1, "1","1"）;
--Successful
insert into ttt2 values（1, "1",null）;
insert into ttt2 values（1, "1",null）;
--Successful
insert into ttt2 values（1, null,"1"）;
insert into ttt2 values（1, null,"1"）;
commit;更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12ORA-01659,ORA-01652错误Oracle数据库字符集研究相关资讯 Oracle NUll

Oracle中NULL值详解（06/20/2014 08:55:49）
Oracle sql_not exists与null的测（12/16/2012 13:11:25）
Oracle 排序之NUll值的排序（11/21/2012 08:16:44）

Oracle使用order by排序null值如何（05/26/2013 13:44:08）
Oracle中的Null值解析（12/10/2012 18:57:56）

本文评论查看全部评论（0）