Joins Involving Primary and Foreign Keys
Foreign and primary key relationships are implemented in RDM by internally maintaining rowid pointers that are used to optimally access the related rows and to easily ensure that referential integrity is enforced. A one-to-many relationship is created between the referenced primary key table and the referencing foreign key table. Thus, only 1 read is needed to access the related row in the primary key table from the referencing row in the foreign key table. This is summarized below.
Efp = Cost of a foreign key to primary key access = 1
The number of reads needed to access the foreign key table rows that reference a particular primary key table row is computed by dividing the cardinality of the primary key table by the cardinality of the foreign key table as follows.
Epf = Cost of a primary key to foreign key access = Cf / Cp
One additional optimization occurs when a foreign key table contains a foreign_key_column = value condition. Since the related primary key is indexed and the related foreign key table rows can be directly accessed from the referenced primary key row the foreign key table rows can quickly be found through an index access to the primary key row and then directly accessing each of the referencing foreign key table rows. The cost for this is summarized below.
Epk = Eeq + Epf
All of these formulas are summarized below in Table 9.
| Access Method | Cost Estimate Computation |
|---|---|
| sequential file scan | Escan = P |
| direct access | Edirect = 1 |
| hashed access | Ehash = 2 |
| index access for column = value | Eeq = D + (C * R)/(.7 * K) + (C * R) |
| index access for column in (v1, v2, ..., vn) | Elist = SUM(cost(column = vi)) for all i: 1..n |
| index access for inequalities | Eineq = D + (C * R)/(.7 * K) + (C * R) |
| index access for LIKE with prefix | Elike = D + ((C * R)/(.7 * K)) + (C * R) |
| foreign key to primary key | Efp = 1 |
| primary key to foreign key | Epf = Cf / Cp |
| to foreign key through primary key | Epk = Eeq + Epf |