The access method cost has two parts: a processing cost and an I/O cost. Depending on the hardware environment, a query can be either CPU or I/O bound. You may want to compare the cost characteristics of your queries to equivalent alternatives.
The database manager estimates processing cost as a result of:
This estimate of the number of rows that remain after applying all the predicates is dependent on assumptions on the distribution of data within the column. For the first column of an index, the database manager records additional information to help it recognize non-uniform data distributions. Otherwise, it assumes that the data values are evenly distributed, and uses the following rules:
For a predicate of the form WHERE column = value: The number of rows is estimated as either ROWCOUNT/COLCOUNT or ROWCOUNT*(1/COLCOUNT); that is, the total number of rows divided by the number of distinct values in the column. The term 1/COLCOUNT is the filter factor.
For a predicate that uses a range operator: The number of rows is estimated using the ratio of the range encompassed in the predicate to the range of values in the column. Thus, if LOW2KEY and HIGH2KEY are respectively 10 and 90, then the predicate column > 70 is given a filter factor of, approximately, (90 - 70)/(90 - 10), or 0.25. Only the first 8 bytes of the column are stored for HIGH2KEY and LOW2KEY so it is important that columns be distinct within the first 8 bytes. (7 bytes if the column is nullable.)
The I/O cost is estimated by the number of index pages, the number of data pages, and the number of directory pages to be read.
For a dbspace scan, the number of index pages read is zero. Otherwise, it is determined from the number of leaf pages and levels in the index (NLEAF and NLEVELS) and the filter factors of the matching predicates.
A dbspace scan reads all data pages. The total number of pages is given by NACTIVE.
If access is through an index, the proportion of pages read depends on the filter factor for the predicates applied through the indexes and on the extent to which the data rows are clustered by the index.