Rotating a Data Structure for Enhanced Retrieval

How to:

Request an Alternate View

Reference:

Usage Notes for Restructuring Data

If you are using certain network or hierarchical data sources such as IMS, CA-IDMS/DB, or FOCUS, you can rotate the data source, creating an alternate view which changes some of the segment relationships and enables you to access the segments in a different order. By reporting from an alternate view, you can do the following:

Change the access path. For example, you can access data in a lower segment more quickly by promoting that segment to a higher level.
Change the path structure of a data source. This option is especially helpful if you wish to create a report using several sort fields that are on different paths in the file. By changing the view of the file hierarchy, all the desired sort fields can be on the same path.

It should be noted that retrieval is controlled by the minimum referenced subtree. For more information, see Understanding the Efficiency of the Minimum Referenced Subtree in the Describing a Group of Fields chapter in the Describing Data With TIBCO WebFOCUS® Language manual.

For example, consider the regular and alternate views below:

Since C is the root segment in the alternate view, particular instances of C can be selected faster.

Syntax: How to Request an Alternate View

To request an alternate view, add the name of a field found in the alternate root segment to the file name in the TABLE command, separated by a period (.):

TABLE FILE filename.fieldname

Reference: Usage Notes for Restructuring Data

If you use a non-indexed field, each segment instance is retrieved until the specified record is found. Therefore, this process is less efficient than using an indexed field.
When you use the alternate view feature on a particular child segment, the data retrieved from that segment is retrieved in physical order, not logical order. This is because the child becomes a root segment for the report request, and there are no logical pointers between the child segments of different parents.
Alternate view on an indexed field is a special case that uses the index for retrieval. When you perform an alternate view on an indexed field, you enhance the speed of retrieval. However, you must include an equality test on the indexed field, for example WHERE (MONTH EQ 1) OR (MONTH EQ 2), in order to benefit from the performance improvement.
A field name specified in an alternate file view may not be qualified or exceed 12 characters.
Automatic Indexed Retrieval (AUTOINDEX) is never invoked in a TABLE request against an alternate file view.

Example: Restructuring Data

Consider the following data structure, in which PROD_CODE is an indexed field:

You could issue the following request to promote the segment containing PROD_CODE to the top of the hierarchy, thereby enabling quicker access to the data in that segment.

TABLE FILE SALES.PROD_CODE
"SALES OF B10 DISTRIBUTED BY AREA"
SUM UNIT_SOLD AND RETAIL_PRICE
BY AREA
WHERE PROD_CODE EQ 'B10'
ON TABLE COLUMN-TOTAL
END