$LOOP_TYPE

The $LOOP_TYPE property specifies how to loop over a formula that contains multiple variables when the formula is used in an OLAP_TABLE SQL function that has the LOOP OPTIMIZED clause. The type of looping can impact performance and the number rows that are returned when the formula contains NA aware functions such as NVL or if NULL TRACKING is disabled. For information on null tracking, see "NA2 Bits and Null Tracking".

Syntax

You add or delete a $LOOP_TYPE property to the most recently defined or considered formula (see DEFINE and CONSIDER commands) using a PROPERTY statement:

To add the property, issue the following statement.

PROPERTY '$LOOP_TYPE' {'DENSE' | 'INNER' | 'OUTER'}
To delete the property, issue the following statement.

PROPERTY DELETE '$LOOP_TYPE'

Parameters

DENSE

Returns variable values for all possible combinations of tuples. If null tracking is not specified for a composite, you get NA values for non-existent data as well as for intentionally null values.

DENSE is similar to a cross join in a SQL SELECT statement. It results in the Cartesian product of all of the base dimensions of the variables.

INNER

(Default) Returns variable values only when a tuple has data in all of the variables. NVL values are not included.

INNER is similar to a SQL inner join.

OUTER

Returns a variable value when the tuple has data in any of the variables. NVL values are included.

OUTER is similar to a SQL outer join.