Nonstop Database, HiRDB Version 9 UAP Development Guide
A high-speed search condition refers to a condition derived from a WHERE clause search condition or an ON search condition in a FROM clause by CNF conversion or condition shifting. When high-speed search conditions are derived, the retrieval performance improves because the rows to be retrieved can be narrowed at an earlier stage.
When HiRDB derives high-speed search conditions, it retains the original search conditions used in the derivation. HiRDB can therefore generate just those derived conditions that are optimal conditions without generating derived conditions that cannot be used to narrow the search.
When HiRDB derives high-speed search conditions, it optimizes the search by considering the new derived conditions when it determines the access path (including the table retrieval method, join method, and join sequence). Therefore, when HiRDB derives high-speed search conditions, the access paths may change as described as follows:
When high-speed search conditions are derived from complex conditions, it takes longer to generate the high-speed search conditions and to evaluate the conditions when the search is executed. Therefore, depending on the SQL statements involved, the performance may actually drop instead of improve.
Whether or not HiRDB derives high-speed search conditions depends on the specification values of the SQL optimization and SQL extension optimizing options. The following table shows the relationships between the SQL optimization and SQL extension optimizing options and deriving high-speed search conditions.
Table 4-18 Relationships between the SQL optimization and SQL extension optimizing options and deriving high-speed search conditions
Type | Derivation source condition | Derived condition | Specified SQL optimization option or SQL extension optimizing option | |
---|---|---|---|---|
Do not derive | Derive#1 | |||
CNF conversion | OR condition for one table | One-table condition | -- | -- |
OR condition extending across two or more tables | One-table condition | G#2 | G | |
Join condition (column=column) is extracted by OR reduction | G#2 | G | ||
Other condition for two or more tables | -- | -- | ||
Condition shifting | Join condition for tables A and B, and condition for table A | One-table condition for table B | -- | G |
Join condition for tables A and B, and join condition for tables A and C | Join condition for tables B and C | -- | -- |
Search at the derivation source | Derivation condition | High-speed search conditions that are derived | Whether derived or not | ||
---|---|---|---|---|---|
Two-table search | Direct product | Join condition (column=column) cannot be extracted by OR reduction. | The search condition of the derivation source includes a one-table condition for the same table as for the one-table condition to be derived. | None | N |
The search condition of the derivation source does not include a one-table condition for the same table as for the one-table condition to be derived. | One-table condition | Y | |||
-- | Join condition (column=column) | -- | |||
Other two-table condition | N | ||||
Join condition (column=column) can be extracted by OR reduction. | The search condition of the derivation source includes a one-table condition for the same table as for the one-table condition to be derived. | None | N | ||
The search condition of the derivation source does not include a one-table condition for the same table as for the one-table condition to be derived. | One-table condition | Y | |||
-- | Join condition (column=column) | Y | |||
Other two-table condition | N | ||||
No direct product | -- | N | |||
Search in three or more tables | All direct product | Join condition (column=column) cannot be extracted by OR reduction. | The search condition of the derivation source includes a one-table condition for the same table as for the one-table condition to be derived. | None | N |
The search condition of the derivation source does not include a one-table condition for the same table as for the one-table condition to be derived. | One-table condition | Y | |||
-- | Join condition (column=column) | -- | |||
Other two-table condition | N | ||||
Join condition (column=column) can be extracted partially by OR reduction. | The search condition of the derivation source includes a one-table condition for the same table as for the one-table condition to be derived. | None | N | ||
The search condition of the derivation source does not include a one-table condition for the same table as for the one-table condition to be derived. | One-table condition | Y | |||
-- | Join condition (column=column) | Y | |||
Other two-table condition | N | ||||
Join condition (column=column) can be extracted by OR reduction. | The search condition of the derivation source includes a one-table condition for the same table as for the one-table condition to be derived. | None | N | ||
The search condition of the derivation source does not include a one-table condition for the same table as for the one-table condition to be derived. | One-table condition | Y | |||
-- | Join condition (column=column) | Y | |||
Other two-table condition | N | ||||
Partially direct product | Join condition (column=column) cannot be extracted by OR reduction. | The search condition of the derivation source includes a one-table condition for the same table as for the one-table condition to be derived. | None | N | |
The search condition of the derivation source does not include a one-table condition for the same table as for the one-table condition to be derived. | One-table condition | Y | |||
-- | Join condition (column=column) | -- | |||
Other two-table condition | N | ||||
Join condition (column=column) can be extracted partially by OR reduction. | The search condition of the derivation source includes a one-table condition for the same table as for the one-table condition to be derived. | None | N | ||
The search condition of the derivation source does not include a one-table condition for the same table as for the one-table condition to be derived. | One-table condition | Y | |||
-- | Join condition (column=column) | Y | |||
Other two-table condition | N | ||||
Join condition (column=column) can be extracted by OR reduction. | The search condition of the derivation source includes a one-table condition for the same table as for the two-table condition to be derived. | None | N | ||
The search condition of the derivation source does not include a one-table condition for the same table as for the two-table condition to be derived. | One-table condition | Y | |||
-- | Join condition (column=column) | Y | |||
Other two-table condition | N | ||||
No direct product | -- | N |
CNF conversion refers to converting conditions joined with OR (disjunctive normal form (DNF) format) into equivalent conditions joined with AND (conjunctive normal form (CNF) format). High-speed search conditions can be derived by applying CNF conversion to WHERE clause search conditions or to ON search conditions in FROM clauses.
The following search conditions are derived by CNF conversion:
Furthermore, the specifications for the SQL optimization options and SQL extension optimizing options determine whether HiRDB derives high-speed search conditions. For details about the relationships between SQL optimization options and SQL extension optimizing options, and deriving of high-speed search conditions, see (1) Application scope of high-speed search conditions.
High-speed search conditions are not derived by CNF conversion if any one of the following conditions applies:
Condition shifting refers to deriving a new condition from two or more conditions.
The methods of deriving search conditions by condition shifting are described as follows.
Furthermore, the specifications for the SQL optimization options and SQL extension optimizing options determine whether HiRDB derives high-speed search conditions. For details about the relationships between SQL optimization options and SQL extension optimizing options, and deriving of high-speed search conditions, see (1) Application scope of high-speed search conditions.
If the search conditions consist of a two-table join condition (column=column only) and a one-table condition that includes the join column, HiRDB derives a one-table condition for the column in the table to be joined. An example is shown as follows:
T1.C1 = T2.C1 AND T1.C1 > 10T1.C1 = T2.C1 AND T1.C1 > 10 AND T2.C1 > 10
The underlined section becomes the derived high-speed search condition.
If the search conditions consist of a two-table join condition (column=column only) and a join condition (column=column only) between a column in one of the two tables and a column in a separate third table, HiRDB derives a new join condition from the remaining two columns that are not linked by a join condition. If correlation names are specified, the tables are viewed as separate tables if the correlation names are different. An example is shown as follows:
T1.C1 = T2.C1 AND T2.C1 = T3.C1T1.C1 = T2.C1 AND T2.C1 = T3.C1 AND T1.C1 = T3.C1
The underlined section is the rapid search condition that was derived.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.