Hi,

as mentioned earlier, I also would put all 3 fields in the index to avoid the table access.

Depending on your selectivity of field3 and the number of NULLs in field2 you may also compress the index.

E.g.

create index table1_i1 on table1 (field3, field2, field1) compress 1; -- or even "compress 2"

What is the output of the following queries?

select count(distinct field3) from table1;
select count(*) from table1 where field2 is null;

If you have many NULLs in field2 and field3 is very selective then you may even create the index the following way:

create index table1_i1 on table1 (field2, field3, field1) compress 1;

Remark: Having an index defined on more than 1 column results in NULLs (in a column) stored in the index if there are other columns which are not null.

Ciao

Clemens

--
To unsubscribe, email: suse-oracle-unsubscribe@(protected)
For additional commands, email: suse-oracle-help@(protected)
Please see http://www.suse.com/oracle/ before posting