Creating index for specific values only

indexoracleperformancequery-performance

We have a query that is called very frequently, so I would like to improve performance, but some colleagues are concerned about the index's size and its impact on inserts an updates. 

SELECT field1, field2 
  FROM table1 
  WHERE field3 = '101'
  AND   field2 = @variable

Table table1 has about 5 million rows, but only about 2000 has field3 = 101.

 CREATE TABLE table1 (
   field1 varchar
   field2 int
   field3 int);

Would it be possible to create and index for (field2, field1) only for these 2000 rows in oracle 11g?
How can I do it?

Best Answer

Database SQL Language Reference - CREATE INDEX

"Oracle does not index table rows in which all key columns are null except in the case of bitmap indexes."

Demo

create table t as select level as field3 from dual connect by level <= 1000000;
alter table t add field3_ind as (case when field3 = 101 then field3 end);
create index t_ix_field3_ind on t (field3_ind); 

select * from t where field3_ind = 101;

Execution Plan

select    segment_name
         ,segment_type
         ,bytes

from      dba_segments 

where     owner        =  'DMARKOVITZ' 
      and segment_name in ('T','T_IX_FIELD3_IND')

order by  segment_name             
;

+-----------------+--------------+----------+
| SEGMENT_NAME    | SEGMENT_TYPE | BYTES    |
+-----------------+--------------+----------+
| T               | TABLE        | 13631488 |
+-----------------+--------------+----------+
| T_IX_FIELD3_IND | INDEX        | 65536    |
+-----------------+--------------+----------+

P.s.
The use of virtual column makes the querying more user friendly.

If the index would have been defined as -

create index t_ix_field3 on t (case when field3 = 101 then field3 end);

then in order to use the index, the query should have looked like -

select * from t where case when field3 = 101 then field3 end = 101;
Related Question