2 Normalizing Non-Relational Data

Virtual Views

Exposing arrays as virtual views is a commonly used technique to handle arrays. It generates a virtual view for every array in the parent record that contains all the array members. Virtual views contain primary key fields from the parent to connect the parent and the virtual view. Optionally, they can also contain all fields from the parent table.

During the import process or when you set the virtual array policy on the table level, Oracle Studio for IMS, VSAM, and Adabas Gateways generates virtual views and names them by appending the array name to the parent name. When an array includes another array, the name of the resulting virtual table consists of the parent name, the array name, and the name of the nested array, as follows:

parentName_arrayName_nestedArrayName

For example, a parent table called STUDENT with an array called COURSE and a nested array called ASSIGNMENTS is represented by the virtual view STUDENT_COURSE_ASSIGNMENTS (see Figure 2-5).

The number of nested-array levels is not limited.

Virtual views include the following:

• The array member columns from the original structure.

• The fields from the parent's first unique key, or all parent fields, depending on the selection you make during the import process or when setting the virtual array policy on the table level.

  If all parent fields are included in the virtual view, the parent's indexes are available in the view definition and can be used for efficient optimization strategies.

  Note:

  Inherited keys lose their uniqueness in the virtual view.

• If the view does not include all parent fields, the primary key fields (if the primary key is not the parent's first unique key).

• If selected, a column called <array_name>_ROWNUM, which identifies the row in the array.

The unique key and <array_name>_ROWNUM columns are generated automatically. Together, they uniquely identify each row in the virtual view and form a unique key.

Figure 2-4 shows the STUDENT_BOOKS virtual view with the two array member columns (ISBN and RETURN_DATE) and the column that identifies the row in the array (BOOK_ROWNUM).

Figure 2-4 Virtual View STUDENT_BOOKS

Description of "Figure 2-4 Virtual View STUDENT_BOOKS"

Oracle Studio for IMS, VSAM, and Adabas Gateways also maintains primary and foreign key definitions that connect between the parent table and the array tables. This allows graphical tools to easily match parent and array.

When working with virtual views, consider the following limitations:

• Virtual views are read-only.

• Virtual views do not support arrays within variants that have a selector field.

Including all parent fields in the virtual view greatly reduces the need for performing join operations because this in itself is an implicit join. In general, the query processor can devise efficient access strategies because Oracle Connect for IMS, VSAM, and Adabas Gateways copies all relevant indexes from the parent to the virtual view.

Oracle Studio for IMS, VSAM, and Adabas Gateways indicates virtual views by using a different colored icon in the Metadata view, as shown in Figure 2-5.

Figure 2-5 Display of Virtual Views in Oracle Studio for IMS, VSAM, and Adabas Gateways

Description of "Figure 2-5 Display of Virtual Views in Oracle Studio for IMS, VSAM, and Adabas Gateways"

Sequential Flattening (Bulk Load of Array Data)

Performing a bulk load of complex data from a non-relational system to a relational database requires a carefully thought-out algorithm that keeps I/O operations at a minimum.

In a bulk load scenario, a method such as Virtual Views requires a full scan of the physical file for every single array. An efficient method of performing this task presents a kind of row-wise normalization, called sequential flattening. This method reads all data in the physical file in a single scan.

Sequential flattening replaces arrays in a non-relational system by a sequence of rows. It maps all the record fields of the non-relational file to a single table that contains both parent and child records. In this way, sequential flattening enables the reception of a stream of data by using a single SELECT statement.

The sequentially flattened view of a complex table is referred to as a single table or sequential view. You can choose to create a sequential view in Oracle Studio for IMS, VSAM, and Adabas Gateways by selecting the Generate sequential view check box during the Metadata Model Selection step of the Metadata Import procedure. The single table is read-only.

The flattened table is called <table>_ST, where <table> is the name of the parent table and ST indicates a single table. For example, if a parent table is called STUDENT, the single table, or sequential view, is called STUDENT_ST.

The structure of the single table is identical to the original table's structure, except that Oracle Connect for IMS, VSAM, and Adabas Gateways removes all array dimensions and adds some control fields. When reading a record, Oracle Connect for IMS, VSAM, and Adabas Gateways performs a tree traversal of the parent and its array hierarchy. Each record in the resulting recordset deals with a specific array member; other arrays are nulled out.

The sequentially flattened single table includes the following columns:

• The parent fields, that is the non-repeating fields.

• The array fields for all arrays within the parent.

• For each array, an optional column called <array_name>_ROWNUM, which identifies the row in the array. This column is generated automatically for the array.

The sequentially flattened single table includes a record (row) for each array record.

See Figure 2-1 for an illustration of arrays and nested arrays.

Figure 2-6 shows the metadata that sequential flattening produces for a data source with three arrays (COURSE, ASSIGNMENTS, and BOOK) in the SQL View window. The window presents read-only information about each of the columns in the table.

Figure 2-6 SQL View of the Single Table's Metadata

Description of "Figure 2-6 SQL View of the Single Table's Metadata"

The following table describes the information presented in this window.

Figure 2-7 shows the actual single table after running a SELECT clause. It contains a column for each row in the preceding SQL view.

Figure 2-7 STUDENT_ST with All Parent and Child Records

Description of "Figure 2-7 STUDENT_ST with All Parent and Child Records"

An SQL describe of this single table produces the following result:

Name                                             Null?              Type
----------------------------------------- ------------------ ------------------
__LEVEL                                       NOT NULL        VARCHAR2(64)
__SEQUENCE                                    NOT NULL        NUMBER(11)
COURSE_ROWNUM                                                 NUMBER(10)
ASSIGNMENTS_ROWNUM                                            NUMBER(10)
BOOK_ROWNUM                                                   NUMBER(10)
ID                                            NOT NULL        NUMBER(8)
FIRST_NAME                                    NOT NULL        CHAR(32)
LAST_NAME                                     NOT NULL        CHAR(32)
DATE_OF_BIRTH                                 NOT NULL        CHAR(8)
NUMOF_COURSES                                 NOT NULL        NUMBER(10)
NUMOF_BOOKS                                   NOT NULL        NUMBER(10)
COURSE_ID                                                     NUMBER(8)
COURSE_TITLE                                                  CHAR(48)
INSTRUCTOR_ID                                                 NUMBER(8)
NUMOF_ASSIGNMENTS                                             NUMBER(10)
ASSIGNMENT_TYPE                                               CHAR(12)
ASSIGNMENT_TITLE                                              CHAR(48)
DUE_DATE                                                      CHAR(8)
GRADE                                                         NUMBER(2,1)
ISBN                                                          CHAR(10)
RETURN_DATE                                                   CHAR(8)

This table shows all the columns of the single table.