Table 4-3 describes the features of the SQL optimizing modes.

Table 4-3 Features of the SQL optimizing modes

SQL optimizing mode	Explanation	Advantages	Disadvantages	Selection method
Optimizing mode 1 based on cost	This is the optimization processing method based on cost for HiRDB versions before Version 06-00. This mode can also be used in HiRDB Version 06-00 and later versions.	Even if HiRDB is upgraded from a version earlier than Version 06-00, searches can be performed with the same access paths used in the earlier version. Access paths are sometimes changed for high-speed retrieval.	The optimal access path cannot always be selected because there are only a few access path candidates. (Access paths are not selected by setting facilities such as hash join as candidates.)	Do not specify the SQL extension optimizing option, or clear the specification for application of optimizing mode 2 based on cost in the SQL extension optimizing option. Some SQL statements always use optimizing mode 2 based on cost. For details, see (2) as follows.
Optimizing mode 2 based on cost	This is the optimization processing method based on cost that is used in HiRDB Version 06-00 and later. This mode is designed for fast retrieval.	High-speed retrieval is possible because this mode selects access paths from candidates that combine hashing to join search and subquery processing.	Optimization processing takes time because this mode performs complex optimization processing.	Specify application of optimizing mode 2 based on cost in the SQL extension optimizing option.

Even if optimizing mode 1 based on cost is being used, optimizing mode 2 based on cost is sometimes forcibly applied. The SQL statements that forcibly apply optimizing mode 2 based on cost are as follows:

• Subquery in the SET clause of the UPDATE statement
• Outer join + (inner) join
• COUNT(*) in a set operation result
• Value expression of the DISTINCT set function
• Specification of the query name of a viewed table or WITH clause to an outer join
• Addition update and partial extraction facility for BLOB data and BINARY data
• HiRDB External Data Access facility
• SQL optimization specification
• Sorting with a value expression with a defined length exceeding 255 bytes
• Retrieve first n records
• Retrieval using the BINARY type
• Retrieval of a viewed table or WITH clause containing an internally derived table that becomes a nesting structure with at least two levels
• Matrix partitioning
• Subquery for a joined table
• Application of the MIN or MAX set function to a repetition column
• Row value constructor
• Subquery in the CASE expression
• POSITION function in which value equation 2 is the BLOB type
• Referential constraint
• Check constraint
• Limit release to allow data with a defined length of 256 bytes or more
• Specification of a table targeted for data update, deletion, or addition in a subquery
• Unnesting facility for repetition column in the FROM clause
• LIMIT clause
• Search in which an internally derived table has two or more nesting layers
• Expansion of the specification location in the query expression body
• Window functions
• SIMILAR predicate

The application condition and an example of each SQL sentence are shown as follows.

(a) Subquery in the SET clause of the UPDATE statement

• When a scalar or line subquery is specified in the SET clause of the UPDATE statement
  Example
  

  UPDATE T1 SET(C1,C2)=(SELECT MAX(C1),MAX(C2) FROM T2) WHERE C3=1

  Note

  The underlined section is the applicable location.

(b) Outer join + (inner) join

• When an (inner) join is specified in the FROM clause
  Example
  

  SELECT T1.C1,T2.C2 FROM T1 INNER JOIN T2 ON T1.C1=T2.C1

  Note

  The underlined section is the applicable location.

• When a table reference that includes LEFT [OUTER] JOIN and any other table reference are delimited with a comma (,) and specified in the FROM clause
  Example
  

  SELECT T1.C1,T2.C2 FROM T1 LEFT OUTER JOIN T2 ON T1.C1=T2.C1, T3 WHERE T1.C1=T3.C1

  Note

  The underlined section is the applicable location.

• When table-reference1 LEFT [OUTER] JOIN table-reference2 is specified in the FROM clause, and LEFT [OUTER] JOIN is nested and specified in table-reference2
  Example
  

  SELECT T1.C1,T2.C2,T3.C2 FROM T1 LEFT OUTER JOIN
    (T2 LEFT OUTER JOIN T3 ON T2.C1=T3.C1)
    ON T1.C1=T3.C1

  Note

  The underlined section is the applicable location.

(c) COUNT(*) in a set operation result

• When the query expression body specified in the FROM clause includes a set operation
  Example
  

  SELECT COUNT(*) FROM (SELECT C1 FROM T1 UNION SELECT C1 FROM T2)

  Note

  The underlined section is the applicable location.

(d) Value expression of the DISTINCT set expression

• When a value expression other than a column specification is specified as an argument of the DISTINCT set function (COUNT, SUM, or AVG)
  Example
  

  SELECT AVG(DISTINCT C1+C2) FROM T1

  Note

  The underlined section is the applicable location.

(e) Specification of the query name of a viewed table or WITH clause to an outer join

• When LEFT [OUTER] JOIN for the query name of a viewed table or WITH clause is specified in the FROM clause, and an internally derived table is created from the query name of that viewed table or WITH clause
  Example
  

  WITH W1(C1,C2) AS (SELECT C1,COUNT(*) FROM T1 GROUP BY C1)
    SELECT W1.C1,W1.C2,T2.C2 FROM W1 LEFT JOIN T2 ON W1.C1=T2.C1

  Note

  The underlined section is the applicable location.

(f) Addition update and partial extraction facility for BLOB and BINARY data

• When BLOB-type data is specified in value expression 1 of the SUBSTR scalar function
  Example
  

  SELECT SUBSTR(C1,1,500) FROM T1

  Note

  The underlined section is the applicable location. C1 is a BLOB-type column.

• When the update target of an UPDATE statement is a BLOB-type column, and a concatenation operation is specified in the update value
  Example
  

  UPDATE T1 SET C1=C1||?

  Note

  The underlined section is the applicable location. C1 is a BLOB-type column.

• When the update target of an UPDATE statement is a BLOB-type column or has the BLOB attribute, and a column or component specification is specified in the update value
  Example
  

  UPDATE T1 SET C1=C2

  Note

  The underlined section is the applicable location. C1 and C2 are BLOB-type columns.

(g) HiRDB External Data Access facility

• When a foreign table is included in a query
  Example
  

  SELECT T1.C1,FT2.C2 FROM T1 LEFT OUTER JOIN FT2
    ON T1.C1=FT2.C1

  Note

  The underlined section is the applicable location. FT2 is a foreign table.

(h) SQL optimization specification

• When an SQL optimization specification for a used index is specified
  Example
  

  SELECT T1.C1 FROM T1 WITH INDEX(idx1) WHERE T1.C2<=500

  Note

  The underlined section is the applicable location.

• When an SQL optimization specification for a join method is specified
  Example
  

  SELECT T1.C1,T2.C2 FROM T1 INNER JOIN BY NEST T2 ON T1.C1=T2.C1

  Note

  The underlined section is the applicable location.

• When an SQL optimization specification for a subquery execution method is specified
  Example
  

  SELECT T1.C1 FROM T1 WHERE T1.C1=ANY
    (HASH SELECT T2.C1 FROM T2 WHERE T2.C2='302S')

  Note

  The underlined section is the applicable location.

(i) Sorting with a value expression with a defined length exceeding 255 bytes

• When a CHAR, VARCHAR, MCHAR, or MVARCHAR expression with a minimum defined length of 256 bytes, or an NCHAR or NVARCHAR expression with a minimum defined length of 128 characters is specified as the sort key item in an ORDER BY clause
  Example 1
  

  SELECT C1,C2 FROM T1 ORDER BY C2

  Note

  The underlined section is the applicable location. C2 is a VARCHAR(300) column.

  Example 2
  

  SELECT C1,C3||C4 FROM T1 ORDER BY 2

  Note

  The underlined section is the applicable location. C3||C4 is an NCHAR(150) value expression.

(j) Retrieve first n records

• When a LIMIT clause is specified directly after an ORDER BY clause
  Example
  

  SELECT PCODE,SQUANTITY FROM STOCK WHERE SQUANTITY>20 ORDER BY 2,1 LIMIT 10

  Note

  The underlined section is the applicable location.

(k) Retrieval using the BINARY type

• When a BINARY-type column is retrieved
  Example
  

  SELECT C1 FROM T1

  Note

  The underlined section is the applicable location. C1 is a BINARY-type column.

(l) Retrieval of a viewed table or WITH clause containing an internally derived table that becomes a nesting structure with at least two levels

• When a FROM clause contains a query specification that specifies the query name of a viewed table or WITH clause, and that viewed table or WITH clause contains a FROM clause for a derived query expression that specifies the viewed table or WITH clause that becomes the internally derived table
  Example
  

  WITH Q1(QC1,QC2) AS (SELECT C1,C2 FROM V1 GROUP BY C1,C2)
   
  SELECT AVG(QC1),QC2 FROM Q1 GROUP BY QC2

  Note

  The underlined section is the applicable location. V1 is the viewed table that becomes the internally derived table.

(m) Matrix partitioning

• When a retrieval, update, deletion, or list operation is performed on a matrix-partitioned table
  Example
  

  SELECT * FROM T1

  Note

  The underlined section is the applicable location. T1 is a matrix-partitioned table.

(n) Subquery for a joined table

• When a query specification containing a joined table is specified and a subquery is specified in the ON search condition of the FROM clause, in the WHERE clause, or in the HAVING clause
  Example
  

  SELECT * FROM T1 LEFT JOIN T2 ON T1.C1=T2.C1
    WHERE T1.C1=ANY(SELECT C1 FROM T3)

  Note

  The underlined section is the applicable location.

(o) Application of the MIN or MAX set function to a repetition column

• When a repetition column in the FLAT specification is specified in the MIN or MAX set function
  Example
  

  SELECT MIN(FLAT(C1)) FROM T1

  Note

  The underlined section is the applicable location. C1 is a repetition column.

(p) Row value constructor

• When a row value constructor is specified
  Example
  

  SELECT * FROM T1 WHERE (C1,C2,C3)>(1,2,3)

  Note

  The underlined section is the applicable location.

(q) Subquery in the CASE expression

• When a subquery is specified in the CASE expression
  Example
  

  SELECT CASE(SELECT C1 FROM T1) WHEN 1 THEN C2 ELSE C1 END FROM T1

  Note

  The underlined section is the applicable location.

(r) POSITION scalar function in which value expression 2 is the BLOB type

• When the BLOB type is specified in value expression 2 of the POSITION scalar function
  Example
  

  SELECT POSITION(? AS BLOB(1K) IN C1) FROM T1

  Note

  The underlined section is the applicable location. C1 is a BLOB-type column.

(s) Referential constraint

• When insertion, update, or deletion is executed for a referenced table or a referencing table
  Example
  

  UPDATE T1 SET C1=?

  Note

  The underlined section is the applicable location. T1 is a referenced table or a referencing table.

(t) Check constraint

• When insertion or update is executed for a column in which a check constraint is defined
  Example
  

  INSERT INTO T1(C1,C2) VALUES(?,?)

  Note

  The underlined section is the applicable location. C1 is the column in which a check constraint is defined.

(u) Limit release to allow data with a defined length of 256 bytes or more

• When one of the following expressions is defined in the GROUP BY clause
  
  • CHAR, VARCHAR, MCHAR, or MVARCHAR type with a defined length of 256 bytes or more
  • NCHAR or NVARCHAR type of 128 bytes or more
  • BINARY type of 256 bytes or more
  Example
  

  SELECT C1,COUNT(*) FROM T1 GROUP BY C1

  Note

  The underlined section is the applicable location. T1.C1 is a character string of 256 bytes or more.

• When one of the following value expressions is specified for an argument of a set function
  
  • CHAR, VARCHAR, MCHAR, or MVARCHAR type with a defined length of 256 bytes or more
  • NCHAR or NVARCHAR type of 128 bytes or more
  • BINARY type of 256 bytes or more
  Example
  

  SELECT MIN(C1) FROM T1

  Note

  The underlined section is the applicable location. T1.C1 is a character string of 256 bytes or more.

• When a query expression body is specified in a viewed table, a WITH clause, or a FROM clause, an internally defined table is created, and one of the following value expressions is specified in the selection expressions of the internally derived table
  
  • CHAR, VARCHAR, MCHAR, or MVARCHAR type of 256 bytes or more
  • NCHAR or NVARCHAR type of 128 bytes or more
  • BINARY type of 256 bytes or more
  Example
  

  WITH W1(C1,C2) AS (SELECT DISTINCT C1,C2 FROM T1)
      SELECT C2,COUNT(*) FROM W1 GROUP BY C2

  Note

  The underlined section is the applicable location. T1.C1 is a character string of 256 bytes or more.

(v) Specification of a table targeted for data update, deletion, or addition in a subquery

• When a table targeted for data update, deletion, or addition is specified in a subquery
  Example 1
  

  UPDATE T1 SET C1=NULL WHERE C1=(SELECT MIN(C1) FROM T1)

  Example 2
  

  DELETE FROM T1 WHERE C1=(SELECT MIN(C1) FROM T1)

  Example 3
  

  INSERT INTO T1(C1,C2) VALUES((SELECT MIN(C1) FROM T1),NULL)

  Note

  The underlined section is the applicable location.

• When a table to which data is to be added is specified in the query expression body of the INSERT statement
  Example
  

  INSERT INTO T1(C1,C2) SELECT C1,C2+1 FROM T1

  Note

  The underlined section is the applicable location.

(w) Unnesting facility for repetition column in the FROM clause

• When FLAT is specified in the FROM clause
  Example
  

  SELECT C1,C2 FROM T1(FLAT(C1,C2)) WHERE C1<10 AND C2 >20

  Note

  The underlined section is the applicable location. C1 and C2 are repetition columns.

(x) LIMIT clause

• When the LIMIT clause is specified
  Example
  

  SELECT PCODE, SQUANTITY FROM STOCK WHERE SQUANTITY > 20 ORDER BY 2, 1 LIMIT 20, 10

  Note

  The underlined section is the applicable location.

(y) Search in which an internally derived table has two or more nesting layers

• When the FROM clause of a query specification that creates an internally derived table also specifies a query specification that becomes an internally derived table
  Example
  

  SELECT AVG(QC1),QC2 FROM(SELECT C1,C2 FROM V1 GROUP BY C1,C2) AS Q1(QC1,QC2)

  Note

  The underlined section is the applicable location. V1 is a view table that becomes an internally derived table.

(z) Expansion of the specification location in the query expression body

• When a set operation is specified in a viewed table, the WITH clause, or the FROM clause and this query creates an internally derived table
  Example
  

  WITH V1(C1,C2) AS (SELECT C1,C2 FROM T1 UNION SELECT C1,C2 FROM T2)
      SELECT C1 FROM V1 WHERE C2>0

  Note

  The underlined section is the applicable location.

• When a set operation is specified in the INSERT statement
  Example
  

  INSERT INTO T3 (C1,C2)
      SELECT C1,C2 FROM T1 UNION ALL SELECT C1,C2 FROM T2

  Note

  The underlined section is the applicable location.

• When a set operation is specified in a subquery
  Example
  

  SELECT C1, C2 FROM T3
      WHERE EXISTS(SELECT C1 FROM T1 EXCEPT SELECT C1 FROM T2)

  Note

  The underlined section is the applicable location.

(aa) Window functions

• When a selection expression contains a window function
  Example
  

   
  SELECT C1,C2,COUNT(*) OVER() FROM T1
   
  Note

  The underlined section is the applicable location.
  

(ab) SIMILAR predicate

• When the SIMILAR predicate is specified
  Example
  

   
  SELECT C1 FROM T1 WHERE C2 SIMILAR TO '%(b|g)%'
   
  Note

  The underlined section is the applicable location.
  

Table 4-4 shows the SQL optimizing modes in which the SQL optimization option and SQL extension optimizing option are valid.

Table 4-4 SQL optimizing modes in which the SQL optimization option and SQL extension optimizing option are valid

SQL optimizing mode	SQL optimization option	SQL extension optimizing option
Optimizing mode 1 based on cost	V
Optimizing mode 2 based on cost	V	V

V: The option is valid in this mode.

: The option is invalid in this mode.

To check the SQL optimizing mode that was selected by the optimization process for each SQL statement, use the access path display utility. For details about the access path display utility, see the HiRDB Version 8 Command Reference manual.

1. When the SQL optimizing mode is changed, the search performance of an SQL statement may drop because the access path is changed. If the environment being used for actual operation does not allow adequate evaluation of performance, Hitachi recommends that you do not change the SQL optimizing mode.
2. If you are installing HiRDB for the first time, Hitachi recommends that you use optimizing mode 2 based on cost. If you are using another SQL extension optimizing option, use it by adding it to optimizing mode 2 based on cost. By using optimizing mode 2 based on cost, you can select access paths capable of retrieving data faster because the optimization process can select many types of access paths.
   Normally, optimizing mode 2 based on cost is applied because it is the default value for the pd_additional_optimize_level operand in the HiRDB system definition. Optimizing mode 2 based on cost is also applied when you use the simple setup tool, the system generator, SPSetup.bat, or an environment setup support tool such as HiRDEF to set up your HiRDB environment.
   
3. If you upgrade HiRDB from a version earlier than 06-00, Hitachi recommends that you continue to use optimizing mode 1 based on cost because you are using HiRDB in the same conditions as before the version upgrade. However, some SQL statements may always use optimizing mode 2 based on cost.
4. Normally, the narrowing condition is considered in the optimization process. However, if a hash join, subquery hash execution is applied to the SQL extension optimizing option and there is no narrowing condition, or if the narrowing condition does not produce much narrowing of the number of rows, a hash join that sets a table with more rows as an inner table may be applied, or a table with more rows may be transferred. In such cases, execute the optimizing information collection utility by using one of the following methods, as necessary. For details about the necessity of executing the optimizing information collection utility, see the manual HiRDB Version 8 Command Reference and verify the performance.
   
   • With data stored in the table, set the optimizing information collection level to lvl1 (specify lvl1 in the -c option) and execute the optimizing information collection utility. When lvl1 is specified, the optimizing information collection utility can be executed in a relatively short time because the utility fetches only information on the number of rows in the table. To fetch the number of rows for all tables in the schema, specify ALL in the -t option.
   • If data cannot be stored in the table or if a test environment is being used, specify the number of rows (NROWS) found in the table used in the actual environment, specify the -s option for each table, and then execute optimization. The following is an example of the specification in the optimization parameter file when the number of rows in the table is set to 1,000:
     # Table optimization information
     NROWS 1000 # Total number of rows in table
     
5. If you are using optimizing mode 1 based on cost, normally you do not need to execute the optimizing information collection utility. But if you do execute the utility, set the optimizing information collection level to lvl1.