Selecting From Alternative Access Methods

Consider the following query from the nsfawards example database on which an UPDATE STATS has been run with HISTOSIZE of 100 and a SAMPLING of up to 25,000 rows per table.

select * from award
 	where award_date = "1995-12-15" and exp_date = "1997-06-30";

The optimizer can choose to use either the key on award_date or the key on exp_date to process this query. It will select the key that it determines will execute the query in the fewest logical disk accesses. It estimates this using the data distribution statistics accumulated by UPDATE STATS. The following table shows the relevant statistics and calculations used by the optimizer for each of the two relational expressions in the above query.

Table 11. Optimizable Statistics Example

Stat/Calculation	award_date = "1995-12-15"	exp_date = "1997-06-30
number of rows in table (C)	132,365	132,365
depth of B-tree (D)	4.54	4.54
number of keys per page (K)	31	31
restriction factor (R)	0.00072	0.00968
estimate of number of result rows (C * R)	95	1281
cost estimate	102	1320
actual number of result rows	94	1582

The estimate of the number of rows that match each of the expressions is based on the operation (in this case "=") and the count of histogram matches. If the histogram count is zero, the number of rows to be returned by an equality condition is equal to the average frequency count of the five lowest histogram entries divided by the cardinality of the table. In this example, both the award_date (1995-12-15) and the exp_date (1997-06-30) values are in the histogram. The histogram distribution count for these columns and the number of sampled rows for the award table can be found using the following queries.

select histo_eq_tot from sys$colhisto 
 	where colname = "award_date" and histo_val = "1995-12-15";
  histo_eq_tot
         18
select histo_eq_tot from sys$colhisto 
 	where colname = "exp_date" and histo_val = "1997-06-30";
  histo_eq_tot
        242
select nosampled from sys$tabstats where tabname = "award";
  nosampled
      25000

Thus, the restriction factor for the award_date = "1995-12-15" expression is 18/25000 = 0.00072 and the restriction factor for the exp_date = "1997-06-30" expression is 242/25000 = 0.00968. The optimizer will choose the award_date key because of its lower cost estimate.