Hitachi

Hitachi Advanced Database Application Development Guide


5.10.3 Number of work tables that are created

This subsection explains by way of examples the number of work tables that are created when an SQL statement is executed. Note that the number of work tables explained here is based on the SQL statements. The number of work tables that are actually created during execution of SQL statements is affected by other factors, including the maximum number of SQL processing real threads in the server definition or client definition (adb_sql_exe_max_rthd_num operand) and the number of data items to be manipulated.

Organization of this subsection

(1) Example 1 (when the ORDER BY clause is specified)

Example SQL statement
SELECT "C1","C2","C3" FROM "T1" ORDER BY "C1" ASC
Explanation:

One work table is created for sort processing by the ORDER BY clause.

(2) Example 2 (when the GROUP BY clause is specified)

Example SQL statement
SELECT "C1","C2" FROM "T1" GROUP BY "C1","C2"
Explanation:

The number of work tables that are created depends on whether global hash grouping is selected as the grouping method.

  • When global hash grouping is applied

    Two work tables are created for processing by the GROUP BY clause.

  • When global hash grouping is not applied

    One work table is created for processing by the GROUP BY clause.

For details about grouping methods, see 5.7 Grouping methods.

(3) Example 3 (when SELECT DISTINCT is specified)

Example SQL statement
SELECT DISTINCT "C1","C2","C3" FROM "T1"
Explanation:

The number of work tables that are created depends on the applied method for processing SELECT DISTINCT.

  • When hash execution is applied

    Two work tables are created for processing SELECT DISTINCT.

  • When work table execution is applied

    One work table is created for processing SELECT DISTINCT.

For details about the method for processing SELECT DISTINCT, see 5.9 Method for processing SELECT DISTINCT.

(4) Example 4 (when the DISTINCT set function is specified)

Example SQL statement
SELECT COUNT(DISTINCT "C1") FROM "T1"
Explanation:

The number of work tables that are created depends on whether global hash grouping is selected as the grouping method.

  • When global hash grouping is applied

    Two work tables are created for processing by the set function.

  • When global hash grouping is not applied

    One work table is created for processing by the set function.

For details about grouping methods, see 5.7 Grouping methods.

(5) Example 5 (when the RANK window function is specified)

Example SQL statement
SELECT "C1",RANK() OVER (PARTITION BY "C2" ORDER BY "C3" ASC) FROM "T1"
Explanation:

One work table is created for the RANK window function processing.

(6) Example 6 (when the GROUP BY and ORDER BY clauses are both specified)

Example SQL statement
SELECT "C1","C2" FROM "T1" GROUP BY "C1","C2" ORDER BY "C1" ASC
Explanation:

The number of work tables that are created depends on whether global hash grouping is selected as the grouping method.

  • When global hash grouping is applied

    A total of three work tables are created. Two are created for processing by the GROUP BY clause, and one is created for processing by the ORDER BY clause.

  • When global hash grouping is not applied

    One work table is created for processing by the GROUP BY clause. Only one work table is created in this example because the GROUP BY and ORDER BY clauses only require sorting to take place once.

For details about grouping methods, see 5.7 Grouping methods.

(7) Example 7 (when the GROUP BY and ORDER BY clauses are both specified)

Example SQL statement
SELECT "C1","C2",COUNT(*) "DC1" FROM "T1"
     GROUP BY "C1","C2" ORDER BY "DC1" ASC
Explanation:

The number of work tables that are created depends on whether global hash grouping is selected as the grouping method.

  • When global hash grouping is applied

    A total of three work tables are created. Two are created for processing by the GROUP BY clause, and one is created for processing by the ORDER BY clause.

  • When global hash grouping is not applied

    A total of two work tables are created. One is created for processing by the GROUP BY clause, and one is created for processing by the ORDER BY clause.

For details about grouping methods, see 5.7 Grouping methods.

(8) Example 8 (when SELECT DISTINCT and the ORDER BY clause are specified)

Example SQL statement
SELECT DISTINCT "C1","C2","C3" FROM "T1" ORDER BY "C1" ASC
Explanation:

One work table is created for sort processing by SELECT DISTINCT.

In this example, only one work table is created because the number of sort processes required for SELECT DISTINCT and the ORDER BY clause is only one.

(9) Example 9 (when the GROUP BY clause and a DISTINCT set function are specified)

Example SQL statement
SELECT "C1",COUNT(DISTINCT "C2") FROM "T1" GROUP BY "C1"
Explanation:

The number of work tables that are created depends on whether global hash grouping is selected as the grouping method.

  • When global hash grouping is applied

    A total of three work tables are created. Two are created for processing by the GROUP BY clause, and one is created for processing by the set function.

  • When global hash grouping is not applied

    One work table is created for processing by the GROUP BY clause. Only one work table is created in this example because the GROUP BY clause and set function only require sorting to take place once.

For details about grouping methods, see 5.7 Grouping methods.

(10) Example 10 (when tables are joined)

Example SQL statement
SELECT "T1"."C1","T1"."C2","T2"."C1","T2"."C2" FROM "T1","T2"
    WHERE "T1"."C1"="T2"."C1"
Explanation:

Three work tables for join processing are created.

(11) Example 11 (multiple table references are specified in the FROM clause)

Example SQL statement
SELECT "DT1"."C1","DT2"."C1"
  FROM (SELECT COUNT("T1"."C1") FROM "T1") AS "DT1"("C1"),
       (SELECT COUNT("T2"."C1") FROM "T2") AS "DT2"("C1")
  WHERE "DT1"."C1">"DT2"."C1"
Explanation:

One work table that is used for table join processing is created.

(12) Example 12 (subquery is specified)

Example SQL statement
SELECT "T1"."C1","T1"."C2","T1"."C3" FROM "T1"
    WHERE "T1"."C1"=(SELECT "T2"."C1" FROM "T2"
                                   WHERE "T2"."C2"="T1"."C2")
Explanation:

The number of work tables that are created depends on whether hash execution is specified as the subquery processing method.

  • When hash execution is applied

    Three work tables are created to process the subquery containing the external reference columns.

  • When hash execution is not applied

    No work table is created to process the subquery containing the external reference columns.

For details about how to process subqueries, see 5.6 How to process subqueries.

(13) Example 13 (IN subqueries are specified)

Example SQL statement
SELECTf"C1","C2","C3" FROM "T1" WHERE "C1" IN (SELECT "C1" FROM "T2")
Explanation:

The number of work tables that are created depends on whether hash execution is specified as the subquery processing method.

  • When hash execution is applied

    Three work tables are created to process the subquery specified in the IN predicate.

  • When hash execution is not applied

    One work table is created to process the subquery specified in the IN predicate.

For details about how to process subqueries, see 5.6 How to process subqueries.

(14) Example 14 (quantified predicates are specified)

Example SQL statement
SELECT "C1","C2","C3" FROM "T1" WHERE "C1"=ANY(SELECT "C1" FROM "T2")
Explanation:

The number of work tables that are created depends on whether hash execution is specified as the subquery processing method.

  • When hash execution is applied

    Three work tables are created to process the subquery specified in the quantified predicate.

  • When hash execution is not applied

    One work table is created to process the subquery specified in the quantified predicate.

For details about how to process subqueries, see 5.6 How to process subqueries.

(15) Example 15 (EXISTS predicate is specified)

Example SQL statement
SELECT "T1"."C1","T1"."C2","T1"."C3" FROM "T1"
    WHERE EXISTS(SELECT * FROM "T2" WHERE "T2"."C2"="T1"."C2")
Explanation:

The number of work tables that are created depends on whether hash execution is specified as the subquery processing method.

  • When hash execution is applied

    Three work tables are created to process the subquery specified in the EXISTS predicate.

  • When hash execution is not applied

    No work table is created to process the subquery specified in the EXISTS predicate.

For details about how to process subqueries, see 5.6 How to process subqueries.

(16) Example 16 (table function derived table is specified)

Example SQL statement
SELECT "C1","C2","C3"
    FROM TABLE(ADB_CSVREAD(MULTISET (SELECT "FNAME" FROM "TFILE"),
                          'COMPRESSION_FORMAT=GZIP;
                           FIELD_NUM=1,2,3;'))
           AS "T1"("C1" INTEGER,"C2" CHAR(10),"C3" DATE)
Explanation:

One work table is created to store the results of the table subquery specified in the multiset value expression. For details about multiset value expressions, see Specification format and rules for multiset value expressions in the manual HADB SQL Reference.

(17) Example 17 (viewed tables are specified)

Example SQL statement
CREATE VIEW "VT1"("C1","C2")
    AS SELECT "T1"."C1","T2"."C1" FROM "T1","T2"
          WHERE "T1"."C2"<="T2"."C2"
SELECT * FROM "VT1" AS "XT1","VT1" AS "XT2"
    WHERE "XT1"."C1">"XT2"."C1"
Explanation:

One work table that is used in the viewed table processing is created.

(18) Example 18 (WITH clauses are specified)

Example SQL statement
WITH "QT1"("C1","C2") AS (SELECT "T1"."C1","T2"."C1" FROM "T1","T2"
                              WHERE "T1"."C2"<="T2"."C2")
SELECT * FROM "QT1" WHERE "C1"=(SELECT MAX("C1") FROM "QT1")
Explanation:

One work table that is used in the WITH clause processing is created.

(19) Example 19 (a set operation is specified)

Example SQL statement
SELECT "C1","C2" FROM "T1" UNION SELECT "C1","C2" FROM "T2"
Explanation:

Two work tables are created to process the set operation.

(20) Example 20 (a recursive query is specified)

Example SQL statement
WITH "QT1"("C1","C2")
  AS (SELECT "C1","C2" FROM "T1" WHERE "C2" BETWEEN 'AA' AND 'EE'
      UNION ALL 
      SELECT "C1"+1,"C2" FROM "QT1" WHERE "C1"<=10)
SELECT * FROM "QT1"
Explanation:

In a recursive query, to retain the results of anchor and recursive members, two work tables that will be used to process the recursive query are created. For details about recursive queries, see Query expression in the manual HADB SQL Reference.

(21) Example 21 (a DISTINCT set function and GROUP BY clause are specified)

Example SQL statement
SELECT "C1",COUNT(DISTINCT "C2"),SUM("C3") FROM "T1"
    GROUP BY "C1"
Explanation:

The number of work tables that are created depends on whether global hash grouping is selected as the grouping method.

  • When global hash grouping is applied

    A total of four work tables are created. Two are created for processing by the GROUP BY clause, and two are created for processing by the set function.

  • When global hash grouping is not applied

    One work table is created for processing by the GROUP BY clause. In the case of this example, no work tables are created for processing by the set function because the sort processing by the GROUP BY clause and the sort processing by the set function are executed concurrently.

For details about grouping methods, see 5.7 Grouping methods.

(22) Example 22 (multiple DISTINCT set functions are specified)

Example SQL statement
SELECT COUNT(DISTINCT "C1"),COUNT(DISTINCT "C2") FROM "T1"
Explanation:

The number of work tables that are created depends on whether global hash grouping is selected as the grouping method.

  • When global hash grouping is applied

    Three work tables are created for processing by the set functions.

  • When global hash grouping is not applied

    Two work tables are created for processing by the set functions.

For details about grouping methods, see 5.7 Grouping methods.