Repetition columns

A repetition column is a column whose data values may consist of multiple elements. An element means each item that is stored in the same row of the repetition column. A column is defined as a repetition column in CREATE TABLE; the number of elements must also be defined (however, the number of elements can be increased later with the ALTER TABLE).

Defining a table to contain repetition columns offers the following benefits:

If multiple tables contain identical information, the overlapping information can be eliminated, thus reducing the amount of disk space that is required.
The need for joining multiple tables is eliminated.
Because related data (repetition data) is stored nearby, access performance is better than when separate tables are accessed.

Figure 3-19 shows an example of a table containing repetition columns. For details about repetition columns, see the HiRDB Version 8 Installation and Design Guide.

Figure 3-19 Example of a table containing repetition columns

[Figure]

Organization of this subsection: (1) Example of defining a repetition column; (2) Operations for repetition columns

(1) Example of defining a repetition column

Following is the CREATE TABLE SQL statement that defines the table containing the repetition column in Figure 3-19:

CREATE TABLE employee list
   NAME NVARCHAR(10),
   QUALIFICATION NVARCHAR(20) ARRAY[10],
   SEX NCHAR(1),
   FAMILY NVARCHAR(5) ARRAY[10],
   RELATIONSHIP NVARCHAR(5) ARRAY[10],
   SUPPORT SMALLINT ARRAY[10]);

(2) Operations for repetition columns

The operations listed below can be performed on a table containing repetition columns.

Retrieval using a structured repetition predicate

A structured repetition predicate is used to perform a search on multiple repetition columns in a table, where the subscripts correspond directly with the elements.

Example 1

SELECT NAME FROM STAFF_TABLE WHERE
       ARRAY(RELATIONSHIP, SUPPORT) [ANY]
       (RELATIONSHIP='Father' AND SUPPORT=1)

Updating a repetition column

The following three repetition column updating methods are provided:

Updating an existing element (SET clause of UPDATE statement)
Adding a new element (ADD clause of UPDATE statement)
Deleting an existing element (DELETE clause of UPDATE statement)

To update a table containing a repetition column, repetition-column-name [{subscript | *}] is used to specify the element of the repetition column that is to be updated.

Example 2: Updating an existing element

UPDATE STAFF_TABLE SET QUALIFICATION[2]=N'Accounting-II'
       WHERE NAME=N'Tom Jones'

Example 3: Adding a new element

UPDATE STAFF_TABLE ADD QUALIFICATION[*]=ARRAY{N'Systems analysis'}
       WHERE NAME=N'Tom Jones'

Example 4: Deleting an existing element

UPDATE STAFF_TABLE DELETE QUALIFICATION[2]
       WHERE NAME=N'Tom Jones'

Other operations for repetition columns: To specify a repetition column in an SQL statement, repetition-column-name [subscript] must be used.

For details about the operations for tables containing repetition columns, see the HiRDB Version 8 UAP Development Guide.

3.3.13 Repetition columns

(1) Example of defining a repetition column

(2) Operations for repetition columns