ID Number: Q75286
1.10
OS/2
Summary:
SYMPTOMS:
If the first column of a composite index has a relatively low
selectivity, the optimizer may not choose to use the index.
CAUSE:
The query optimizer in SQL Server version 1.1 maintains data
distribution statistics only on the first column of a composite
index. This results in only the first column being considered when
computing the weights for index selection.
RESOLUTION:
Make sure the first column has the highest selectivity when you
are creating a composite index.
More Information:
In the following example, the selectivity of the first column of the
table is low. That is, there are only two unique values. Because of
this, when the composite index is created in the order of (Col1, Col2)
and the first query is executed, the optimizer will choose not to use
the index. However, when the index is dropped and re-created in the
reverse order of (Col2, Col1), the selectivity of the first column of
the index is now higher, and the optimizer will choose to use the
index.
Example
-------
create table ind_test (col1 int, col2 int)
go
insert into ind_test values (1,1)
insert into ind_test values (1,2)
insert into ind_test values (1,3)
insert into ind_test values (2,1)
insert into ind_test values (2,2)
insert into ind_test values (2,3)
go
create clustered index ind_test_idx on ind_test (col1, col2)
go
set showplan on
go
select * from ind_test where col2 = 2
go
set showplan off
go
drop index ind_test.ind_test_idx
go
create clustered index ind_test_idx on ind_test (col2, col1)
go
set showplan on
go
select * from ind_test where col2 = 2
go
set showplan off
go
drop table ind_test
go
Results
-------
STEP 1
(Msg 6201, Level 0, State 1).
The type of query is SELECT
(Msg 6203, Level 0, State 1).
FROM TABLE
(Msg 6215, Level 0, State 1).
ind_test
(Msg 6217, Level 0, State 1).
Nested iteration
(Msg 6219, Level 0, State 1).
Table Scan <-- Index is not used.
(Msg 6223, Level 0, State 1).
col1 col2
----------- -----------
1 2
2 2
(2 rows affected)
STEP 1
(Msg 6201, Level 0, State 1).
The type of query is SETOFF
(Msg 6203, Level 0, State 1).
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
STEP 1
(Msg 6201, Level 0, State 1).
The type of query is SELECT
(Msg 6203, Level 0, State 1).
FROM TABLE
(Msg 6215, Level 0, State 1).
ind_test
(Msg 6217, Level 0, State 1).
Nested iteration
(Msg 6219, Level 0, State 1).
Using Clustered Index --> Index is used.
(Msg 6224, Level 0, State 1).
col1 col2
----------- -----------
1 2
2 2
(2 rows affected)
STEP 1
(Msg 6201, Level 0, State 1).
The type of query is SETOFF
(Msg 6203, Level 0, State 1).
(0 rows affected)
(0 rows affected)