This statement creates a composite index on titles:
create index composite_ix on titles (pub_id, type, price)
Both of the following clauses can be used to position the start of the search and to limit the end point, since the leading columns are specified:
where pub_id = "P099"
where pub_id = "P099" and type = "news"
The first example requires reading all the index pages where pub_id equals “P099”, while the second reads only the index pages where both conditions are true. In both cases, these queries need to read the data rows for each of the index rows that are examined, so the scan and filter selectivity are the same.
In the following example, the query needs to read all of the index leaf-level pages where pub_id equals “P099”, as in the queries above. But in this case, Adaptive Server examines the value for price, and needs to read only those data pages where the price is less than $50:
where pub_id = "P099" and price < $50
In this case, the scan and filter selectivity differ. If column-level statistics exist for price, the optimizer combines the column statistics on pub_id and price to determine the filter selectivity, otherwise the filter selectivity is estimated using the default range selectivity.
In the dbcc traceon(302) output below, the selectivity for the price column uses the default value, 0.33, for an open range. When combined with the selectivity of 0.031400 for pub_id, it yields the filter selectivity of 0.010362 for composite_ix:
Selecting best index for the SEARCH CLAUSE:
titles.price < 50.00
titles.pub_id = ’P099’
Estimated selectivity for pub_id,
selectivity = 0.031400, upper limit = 0.031400.
No statistics available for price,
using the default range selectivity to estimate selectivity.
Estimated selectivity for price,
selectivity = 0.330000.
Estimating selectivity of index ’composite_ix’, indid 6
scan selectivity 0.031400, filter selectivity 0.010362
52 rows, 57 pages, index height 2,
Data Row Cluster Ratio 0.013245,
Index Page Cluster Ratio 1.000000,
Data Page Cluster Ratio 0.100123
