2

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?

3

1 Answer 1

6

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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.