Example of function-based index

Tom Kyte Blog - asktom.oracle.com

My point is, the optimizer thinks that very few rows will be returned by the FBI, that will be the index it really wants to use to access this table then. 0 or 1 regardless. 0 would be even better than 1. This is actually an example I use in my seminars when talking about indexing actually. ops$tkyte%ORA11GR1> create table t as 2 select 'Y' processed_flag, a.* from stage a; Table created. ops$tkyte%ORA11GR1> create or replace view v 2 as 3 select t.*, 4 case when processed_flag = 'N' then 'N' 5 else NULL 6 end processed_flag_indexed 7 from t; View created. ops$tkyte%ORA11GR1> ops$tkyte%ORA11GR1> create index t_idx on 2 t( case when processed_flag = 'N' then 'N' 3 else NULL 4 end ); Index created. ops$tkyte%ORA11GR1> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true ); PL/SQL procedure successfully completed. ops$tkyte%ORA11GR1> ops$tkyte%ORA11GR1> update t set processed_flag = 'N' 2 where rownum <= 100; 100 rows updated. ops$tkyte%ORA11GR1> ops$tkyte%ORA11GR1> select num_rows from user_indexes where index_name = 'T_IDX'; NUM_ROWS ---------- 0 ops$tkyte%ORA11GR1> select num_rows from user_tables where table_name = 'T'; NUM_ROWS ---------- 67522 ops$tkyte%ORA11GR1> select rowid, object_name 2 from v 3 where processed_flag_indexed = 'N' 4 and rownum = 1; ROWID OBJECT_NAME ------------------ ------------------------------ AAATsrAAEAAAc98AAA ICOL$ Execution Plan ---------------------------------------------------------- Plan hash value: 2869526954 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 38 | 1 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM=1) 3 - access(CASE WHEN "PROCESSED_FLAG"='N' THEN 'N' ELSE NULL END ='N') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads and the optimizer goes right for that index, it knows "few rows"