INF: Indexes Use Distribution Page Statistics
ID: Q75827
|
The information in this article applies to:
-
Microsoft SQL Server for OS/2, version 4.2
SUMMARY
The following steps illustrate that distribution statistics are used
when only a prefix of the index is used in the query.
MORE INFORMATION- Create a table and index as follows:
create table t1 (c1 varchar(20), c2 varchar(20), c3 char(30) )
create index i1 on t1 (c1,c2)
The third column ensures that the table occupies enough pages to
keep the optimizer from always choosing a table scan.
- Populate the table with 200 rows of generated data with the
following criteria:
- Generate all 200 values of c2 using a counter.
- Set c1=1 in the first 100 rows.
- Increment the value of c1 using a counter for the next 100 rows.
With the table populated in this way, c1 will not be very selective
below row 100; however, it will be very selective above 100.
- Next, run update statistics and the following queries with SET
SHOWPLAN ON:
select * from t1 where c1=1
select * from t1 where c1=150
Note that the first query scans the table instead of using the
index. This proves that the optimizer is aware that the index is
very unselective when c1=1. In contrast, the second query uses the
index. This indicates that the optimizer is also aware that the
index is very selective when c1=150.
Additional query words:
Transact-SQL dblib
Keywords : kbprg SSrvDB_Lib SSrvTrans SSrvServer
Version : 4.2
Platform : OS/2
Issue type :
|