Today i was reading http://tonguc.wordpress.com/2008/06/26/oracle-vs-sql-server-a-never-ending-story/ Tonguç’s blog, I really liked MS’s filtered index idea. It could be possible to use it in oracle with a workaround.

-- clean
drop table t_fact;
drop function filter_tfact_ntype;

-- create a dummy table, ntype column is our guy
create table t_fact (nid primary key, vfiller, ntype) nologging
as
select level, cast(null as char(20)), mod(level, 20) from dual connect by level  USER, indname => 'idx_fact_filtered');
dbms_stats.gather_index_stats(ownname => USER, indname => 'idx_fact_boring');
end;
/

-- a conventional idx
create index idx_fact_boring on t_fact(ntype);

-- a filtered idx, in fact it is a function based one
create or replace function filter_tfact_ntype(p_cell in number) return number
deterministic
as
begin
if p_cell in (3,6,9) then
   return p_cell;
else return null;
end if;
end;
/
CREATE INDEX idx_fact_filtered on t_fact(filter_tfact_ntype(ntype));

-- learn what we have in idxes
begin
dbms_stats.gather_index_stats(ownname => USER, indname => 'idx_fact_filtered');
dbms_stats.gather_index_stats(ownname => USER, indname => 'idx_fact_boring');
end;
/

-- filtered one kinda 3/20 size of boring one
SELECT blevel, leaf_blocks, distinct_keys, index_name
FROM user_indexes
where index_name in (upper('idx_fact_filtered'), upper('idx_fact_boring'));

    BLEVEL     LEAF_BLOCKS DISTINCT_KEYS   INDEX_NAME
   ---------- -----------    -------------      ------------------------------
         1          39             20                      IDX_FACT_BORING
         1           6              3                       IDX_FACT_FILTERED

-- i know it is not a acceptable SQL, just experimental

-- using filtered
select * from t_fact where (3 not in (3,6,9) or filter_tfact_ntype(t_fact.ntype) = 3)
and (3 in (3,6,9) or t_fact.ntype = 3);

-- using normal
select * from t_fact where (4 not in (3,6,9) or filter_tfact_ntype(t_fact.ntype) = 4)
and (4 in (3,6,9) or t_fact.ntype = 4)