只对部分行建立索引

只对部分行建立索引 
创建大表: 
create table t_big (id int,flag varchar2(5));

begin 
for j in 0..100 loop 
for i in 0..10000 loop 
execute immediate 'insert into t_big(id) values(:1) ' using i; 
end loop; 
commit; 
end loop; 
end; 
/

insert into t_big select r,case when mod(r,50)=0 then 'N' else 'Y' end from (select level r from dual connect by level<500); 
commit;

创建索引 
create index idx_flag on t_big(case flag when 'N' then 'N' end);

SQL> analyze index idx_flag validate structure;

Index analyzed. 
SQL&gt; select name,btree_space,lf_rows,height from index_stats;

NAME BTREE_SPACE LF_ROWS HEIGHT 
------------------------------ ----------- ---------- ---------- 
IDX_FLAG 7996 9 1

使用: 
select * from t_big where decode(flag,'N','N')='N';