5.12.1 Tips for searching an archivable multi-chunk table

This subsection explains by way of examples the process of searching an archivable multi-chunk table.

The definition of the archivable multi-chunk table used in these examples and the archived state of the data are as follows:

▪ Definition of archivable multi-chunk table

[Figure]

▪ Archived state of data

The database stores data from April 2015 to March 2016.
The data from April to December 2015 is archived.
The data from January to March 2016 is not archived.

Organization of this subsection

(1) Basic concept when searching archivable multi-chunk tables
(2) Specifying search conditions
(3) When searching unarchived data
(4) When searching archived data

(1) Basic concept when searching archivable multi-chunk tables

When searching an archivable multi-chunk table, you need to narrow the scope of the search by specifying the datetime information for the archive range column as a search condition.

You also need to be aware of whether the data you are searching for is archived. Search processing can take longer when searching archived data.

To Page Top

(2) Specifying search conditions

The following are the key considerations when specifying search conditions to search an archivable multi-chunk table:

Narrow down the search range by specifying a condition that specifies the archive range column as a search condition in a WHERE clause.

Example:
```
SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" BETWEEN DATE'2016/02/01' AND DATE'2016/02/29'
```
You must specify the underlined portion. This narrows the search range using the datetime information for the archive range column.

Note that restrictions apply to the predicates you can specify. For details, see 5.12.2 Using the datetime information of the archive range column to narrow the search range.

Narrow the search range further by adding AND conditions.

Example:

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" BETWEEN DATE'2016/02/01' AND DATE'2016/02/29'
    AND "C1"='P001'
    AND "C2"=100

We recommend that you specify literals as the comparison conditions for the archive range column.

Example: Example of recommended specification
```
WHERE "RECORD-DAY" BETWEEN DATE'2016/01/01' AND DATE'2016/03/31'
WHERE "RECORD-DAY" >= DATE'2016/02/01'
```

To Page Top

(3) When searching unarchived data

When searching unarchived data, specify search conditions a way that limits the search range to the unarchived data. In this example, the unarchived data is data from January 2016.

Example:

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" >= DATE'2016/01/01'
    AND "C1"='P001'
    AND "C2"=100

The search condition specified in this example restricts the search range to data from January 1st, 2016.

To Page Top

(4) When searching archived data

When searching archived data, the datetime information specified for the archive range column in the search condition preferably references as narrow a range as possible. In this example, the archived data is data from April 2015 to December 2015.

Example:

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" BETWEEN DATE'2015/10/01' AND DATE'2015/10/05'
    AND "C1"='P001'
    AND "C2"=100

Narrow the search range as much as possible by specifying the underlined portion. This reduces the number of archive files that need to be read. The search time increases in proportion to the number of archive files HADB has to read.

To Page Top