5.12.3 Notes about specifying JOIN (joined table)

When you specify a joined table, depending on how the joined table is specified, the search range might not be narrowed by the datetime information of the archive range column. The following explains how to specify joined tables in such a way that the search range is narrowed.

In the examples, ARCHIVE-T1 represents the archivable multi-chunk table, and RECORD-DAY represents the archive range column.

Organization of this subsection

(1) Example 1 (LEFT OUTER JOIN)
(2) Example 2 (RIGHT OUTER JOIN)
(3) Example 3 (FULL OUTER JOIN)

(1) Example 1 (LEFT OUTER JOIN)

If you specify the archivable multi-chunk table in the table reference on the right side of LEFT OUTER JOIN, the search range is not narrowed using the datetime information of the archive range column.

▪ Example where search range is not narrowed (before)

SELECT "T1"."C1" FROM "T1"
                 LEFT OUTER JOIN "ADBUSER01"."ARCHIVE-T1" AS "DT"
                 ON "T1"."C1" = "DT"."C1"
  WHERE "RECORD-DAY" BETWEEN DATE'2016/01/15' AND DATE'2016/02/15'
  AND "C1"='P001'

Explanation:: If you specify the archivable multi-chunk table in the table reference on the right side of LEFT OUTER JOIN as underlined, the search range is not narrowed using the datetime information of the archive range column. In this case, searches will target all archived data and might take longer as a result.

To avoid this issue, modify the SQL statement as follows:

▪ Example where search range is narrowed (after)

SELECT "T1"."C1" FROM "T1"
                 LEFT OUTER JOIN (SELECT * FROM "ADBUSER01"."ARCHIVE-T1"
                                    WHERE "RECORD-DAY"
                                      BETWEEN DATE'2016/01/15' AND DATE'2016/02/15'
                                    AND "C1"='P001') AS "DT"
                 ON "T1"."C1" = "DT"."C1"
  WHERE "RECORD-DAY" BETWEEN DATE'2016/01/15' AND DATE'2016/02/15'
  AND "C1"='P001'

Explanation:: As underlined, the search condition that specifies the archive range column is replaced with an explicitly specified derived table. When the SQL statement is amended in this way, the search range is narrowed using the datetime information of the archive range column.

Note: No particular action is required if you specify the archivable multi-chunk table in the table reference on the left side of LEFT OUTER JOIN. That is, you do not need to modify the SQL statement.

To Page Top

(2) Example 2 (RIGHT OUTER JOIN)

If you specify the archivable multi-chunk table in the table reference on the left side of RIGHT OUTER JOIN, the search range is not narrowed using the datetime information of the archive range column.

▪ Example where search range is not narrowed (before)

SELECT "T1"."C1" FROM "ADBUSER01"."ARCHIVE-T1" AS "DT"
                 RIGHT OUTER JOIN "T1"
                 ON "DT"."C1" = "T1"."C1"
  WHERE "RECORD-DAY" BETWEEN DATE'2016/01/15' AND DATE'2016/02/15'
  AND "C1"='P001'

Explanation:: If you specify the archivable multi-chunk table in the table reference on the left side of RIGHT OUTER JOIN as underlined, the search range is not narrowed using the datetime information of the archive range column. In this case, searches will target all archived data and might take longer as a result.

To avoid this issue, modify the SQL statement as follows:

▪ Example where search range is narrowed (after)

SELECT "T1"."C1" FROM (SELECT * FROM "ADBUSER01"."ARCHIVE-T1"
                         WHERE "RECORD-DAY" BETWEEN DATE'2016/01/15' AND DATE'2016/02/15'
                         AND "C1"='P001') AS "DT"
                 RIGHT OUTER JOIN "T1"
                 ON "DT"."C1" = "T1"."C1"
  WHERE "RECORD-DAY" BETWEEN DATE'2016/01/15' AND DATE'2016/02/15'
  AND "C1"='P001'

Explanation:: As underlined, the search condition that specifies the archive range column is replaced with an explicitly specified derived table. When the SQL statement is amended in this way, the search range is narrowed using the datetime information of the archive range column.

Note: No particular action is required if you specify the archivable multi-chunk table in the table reference on the right side of RIGHT OUTER JOIN. That is, you do not need to modify the SQL statement.

To Page Top

(3) Example 3 (FULL OUTER JOIN)

If you specify the archivable multi-chunk table in the table reference on the left or right side of FULL OUTER JOIN, the search range is not narrowed using the datetime information of the archive range column.

▪ Example where search range is not narrowed (before)

SELECT "T1"."C1" FROM "ADBUSER01"."ARCHIVE-T1" AS "DT"
                 FULL OUTER JOIN "T1"
                 ON "DT"."C1" = "T1"."C1"
  WHERE "RECORD-DAY" BETWEEN DATE'2016/01/15' AND DATE'2016/02/15'
  AND "C1"='P001'

Explanation:: If, as underlined, you specify the archivable multi-chunk table in the table reference on the left or right side of FULL OUTER JOIN, the search range is not narrowed using the datetime information of the archive range column. In this case, searches will target all archived data and might take longer as a result.

To avoid this issue, modify the SQL statement as follows:

▪ Example where search range is narrowed (after)

SELECT "T1"."C1" FROM (SELECT * FROM "ADBUSER01"."ARCHIVE-T1"
                         WHERE "RECORD-DAY" BETWEEN DATE'2016/01/15' AND DATE'2016/02/15'
                         AND "C1"='P001') AS "DT"
                 FULL OUTER JOIN "T1"
                 ON "DT"."C1" = "T1"."C1"
  WHERE "RECORD-DAY" BETWEEN DATE'2016/01/15' AND DATE'2016/02/15'
  AND "C1"='P001'

Explanation:: As underlined, the search condition that specifies the archive range column is replaced with an explicitly specified derived table. When the SQL statement is amended in this way, the search range is narrowed using the datetime information of the archive range column.

To Page Top