Composite Indexes
I have a composite index defined like the following :
create index index_1 on table_1 (col1, col2, col3, col4, col5);
Select statement is then run :
select count(*)
from table_1
where col1 = 1
and col2 = 2
and col3 is not null
and col4 = 4
and col5 = 5
The statement fails with no rows found (even though it should succeed).
Index is altered to :
create index index_1 on table_1 (col1, col2, col4, col5);
create index index_2 on table_1 (col3);
and select then works.
Is there a problem with handling NULLs in a composite index?
Platform is HP-BLS, product is OnLine/Secure v5.0 UD4
BTW, tried the above an another machine and results are the other way round, ie
the first index structure works, the second foesn't.
What gives?
--
---------------------------------------
Rizzo rizzo@fourgee.demon.co.uk
---------------------------------------