5.12.4 Equivalent exchange of SQL statements that search archivable multi-chunk tables

When all of the following conditions are met, the HADB server uses equivalent exchange to automatically transform SQL statements that search archivable multi-chunk tables.

• When searching archived data

• A condition that specifies the archive range column is specified as a search condition in a WHERE clause.

• The search condition specified in the WHERE clause complies with the rules explained in 5.12.2 Using the datetime information of the archive range column to narrow the search range.

Note

• The SQL statement after equivalent exchange is output to the access path information.

• The rules for SQL statements are applied to the SQL statement after equivalent exchange.

The following shows an example of equivalent exchange.

Example of specified SELECT statement

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" BETWEEN DATE'2015/11/01' AND DATE'2016/01/31'
    AND "C1"='P001'

Search range used by this SELECT statement

Example of SELECT statement after equivalent exchange

SELECT * FROM 
  (SELECT * FROM "ARCHIVE-T1"                             ...1
   UNION ALL                                              ...2
   SELECT * FROM                                          ...3
     TABLE (ADB_CSVREAD(MULTISET(                         ...4
       SELECT "ARCHIVE_FILE_NAME" FROM
                  "HADB"."LOCATION_TABLE_00020191" AS "LOC"
                 ,"HADB"."STATUS_CHUNKS" AS "SCK"
         WHERE "RANGE_MAX" >= DATE'2015/11/01'            ...5
           AND "RANGE_MIN" <= DATE'2016/01/31'            ...5
           AND "SCK"."TABLE_SCHEMA" = 'ADBUSER01'
           AND "SCK"."TABLE_NAME" = 'ARCHIVE-T1'
           AND "SCK"."CHUNK_ID" = "LOC"."CHUNK_ID"
           AND "SCK"."CHUNK_STATUS" IS NULL
       )
       ,'omitted')
     ) AS "TBLFUNC_00020191" ("C1" VARCHAR(10), "C2" INT, "RECORD_DAY" DATE)   ...6
  )
    WHERE "RECORD_DAY" BETWEEN DATE'2015/11/01' AND DATE'2016/01/31'
    AND "C1"='P001'

Explanation:

The SQL statement in this example searches archived data and data that is not archived. In this example, the SQL statement is rewritten into a query that searches archived data and a query that searches unarchived data, and the union of these two queries is determined by a UNION ALL operator.

1. A query that searches unarchived data

2. Determines the union (UNION ALL) of the queries labeled 1 and 3

3. A query that searches archived data

4. This part is converted to an ADB_CSVREAD function that reads the archive files that store the archived data.

5. The search condition specified in the WHERE clause is rewritten as a search condition that searches the location table.^#

6. TBLFUNC_00020191 is the correlation name of the table function derived table. Correlation names are determined according to the following conventions:

   TBLFUNC_nnnnnnnn

   nnnnnnnn: An 8 character string (0 to 9 and A to F) obtained by converting the table ID of the archivable multi-chunk table to hexadecimal

#

Examples of rewriting the search condition as a search condition that searches the location table are as follows:

Example 1:

Example 2:

• RANGE_MAX

  A column in the location table. RANGE_MAX is the maximum value in the archive range column for each archive file.

• RANGE_MIN

  A column in the location table. RANGE_MIN is the minimum value in the archive range column for each archive file.

Note

The internal derived tables generated by equivalent exchange of SQL statements that search archivable multi-chunk tables are not subject to expansion. For details about the expansion of internal derived tables, see Internal derived tables in the manual HADB SQL Reference.

To Page Top