5.10.3 Number of work tables that are created

SELECT "C1","C2","C3" FROM "T1" ORDER BY "C1" ASC

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

SELECT "C1","C2" FROM "T1" GROUP BY "C1","C2"

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.

SELECT DISTINCT "C1","C2","C3" FROM "T1"

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.

SELECT COUNT(DISTINCT "C1") FROM "T1"

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.

SELECT "C1",RANK() OVER (PARTITION BY "C2" ORDER BY "C3" ASC) FROM "T1"

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

SELECT "C1","C2" FROM "T1" GROUP BY "C1","C2" ORDER BY "C1" ASC

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.

SELECT "C1","C2",COUNT(*) "DC1" FROM "T1"
     GROUP BY "C1","C2" ORDER BY "DC1" ASC

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.

SELECT DISTINCT "C1","C2","C3" FROM "T1" ORDER BY "C1" ASC

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.

SELECT "C1",COUNT(DISTINCT "C2") FROM "T1" GROUP BY "C1"

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.

SELECT "T1"."C1","T1"."C2","T2"."C1","T2"."C2" FROM "T1","T2"
    WHERE "T1"."C1"="T2"."C1"

Three work tables for join processing are created.

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"

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

SELECT "T1"."C1","T1"."C2","T1"."C3" FROM "T1"
    WHERE "T1"."C1"=(SELECT "T2"."C1" FROM "T2"
                                   WHERE "T2"."C2"="T1"."C2")

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.

SELECTf"C1","C2","C3" FROM "T1" WHERE "C1" IN (SELECT "C1" FROM "T2")

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.

SELECT "C1","C2","C3" FROM "T1" WHERE "C1"=ANY(SELECT "C1" FROM "T2")

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.

SELECT "T1"."C1","T1"."C2","T1"."C3" FROM "T1"
    WHERE EXISTS(SELECT * FROM "T2" WHERE "T2"."C2"="T1"."C2")

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.

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)

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.

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"

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

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")

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

SELECT "C1","C2" FROM "T1" UNION SELECT "C1","C2" FROM "T2"

Two work tables are created to process the set operation.

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"

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.

SELECT "C1",COUNT(DISTINCT "C2"),SUM("C3") FROM "T1"
    GROUP BY "C1"

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.

SELECT COUNT(DISTINCT "C1"),COUNT(DISTINCT "C2") FROM "T1"

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.