Table Access Methods

RDM SQL provides a variety of methods for retrieving the rows in a table. Each of these access methods is described below, including how cost is estimated for each method. The cost estimate equations use the above statistics as represented by the following parameters.

Table 8. Table Access Method Cost Estimation Parameters
Parameter	Definition
P	The number of pages in the file in which the table's rows are stored.
D	The depth of the B-tree index.
C	The cardinality of the table being accessed (that is, the number of rows in the table).
C_f	The cardinality of the table containing the referenced foreign key.
C_p	The cardinality of the table containing the referenced primary key.
K	The maximum number of key values per index page.
R	The restriction factor, an estimate (between 0 and 1) of the percentage of the rows of the table that satisfy the conditional expression. The restriction factor for a conditional expression is the product of the restriction factors for each relational expression in the conditional expression's boolean product (i.e., rel_expr and rel_expr …)

Database access is performed by reading data and index file pages. A data file page contains one or more table rows so each physical disk read will read that number of rows. An index file page contains many keys per page depending on the size of the page and the size of the index values. RDM uses a B-tree structure for its indexes, which guarantees that each index page is at least half full. On the average, index pages are about 60-70% full. The depth of a B-tree indicates the number of index pages that must be read to locate a particular key value. Most B-trees have a depth of from 4 to 7 levels. A hash index can usually locate a key value in 1 to 3 reads depending on the quality of the hash and the number of key values (rows). Access to a row through a ROWID PRIMARY KEY takes only 1 read and is the fastest access method.