HiRDB features an optimization facility that improves the retrieval efficiency of SQL statements.

Optimization processing includes SQL optimizing modes that use different methods. HiRDB determines the SQL optimizing mode for each SQL based on the specified value of the SQL extension optimizing option and the SQL syntax.

The SQL optimizing mode types are as follows:

• Optimizing mode 1 based on cost (optimization processing method used in HiRDB versions before Version 06-00)
• Optimizing mode 2 based on cost (optimization processing method used in HiRDB versions starting from Version 06-00)

You can also consider the status of the database and specify an optimization method to determine the most efficient access path. There are three types of optimization methods:

• SQL optimization specifications
• SQL optimization options
• SQL extension optimizing options

SQL optimization specifications

SQL optimization specifications can be specified in SQL statements. These optimization methods are applied to the SQL statements that specify the methods.

For details about SQL optimization specifications, see the manual HiRDB Version 8 SQL Reference.

SQL optimization options and SQL extension optimizing options

The SQL optimization options and the SQL extension optimizing options are assigned multiple functions from which you can select those that are necessary. The functions specified by using the SQL optimization options are effective with both optimizing mode 1 based on cost and optimizing mode 2 based on cost. The functions specified with the SQL extension optimizing options are effective with only optimizing mode 2 based on cost.

For details about the SQL optimization options and SQL extension optimizing options, see 6.6.4 Environment definition information.

Notes

Indicators for selecting the SQL optimizing mode are described as follows:

When installing HiRDB for the first time with Version 06-00 or a later version

• Hitachi recommends that you use optimizing mode 2 based on cost.
• If you use optimizing mode 2 based on cost, execute the optimizing information collection facility as necessary to further improve the optimizing precision. For details about the necessity of executing the optimizing information collection utility, see the manual HiRDB Version 8 Command Reference.
• The SQL optimization option and the SQL extension optimizing option have recommended values that should be specified. Make sure that these recommended values are specified, and also examine whether other functions can be used.

When upgrading a HiRDB version earlier than Version 06-00

Hitachi recommends that you use optimizing mode 1 based on cost so that you can use the HiRDB system under the same conditions as before the version upgrade. However, because some SQL statements always use optimizing mode 2 based on cost, study the specification values of the SQL extension optimizing option when you start a new operation in the environment that is already constructed. Also, do not change the specification values of the SQL optimization option.

Organization of this section

4.5.1 SQL optimizing modes

4.5.2 Optimization method types

4.5.3 Specifying SQL optimization

4.5.4 Allocating floatable servers (HiRDB/Parallel Server only)

4.5.5 Grouping processing methods (HiRDB/Parallel Server only)

4.5.6 Join methods

4.5.7 Search Methods

4.5.8 Execution of subqueries with no external references

4.5.9 Execution of subqueries with external references

4.5.10 Preparing for application of hash join and subquery hash execution

4.5.11 Deriving high-speed search conditions