Looping Nature of OLAP DML Statements

Unlike SQL statements that operate against a single row in a table, OLAP DML statements usually operate against the all of the array elements that are in status for the object data object:

When you issue a statement against an object that has one or more dimensions, the statement loops over the values in status for each dimension of the object and performs the requested operation.
When you use an OLAP assignment statement (that is, SET) to assign values to a variable, Oracle OLAP loops through all of the in status array elements assigning values in sequence.

Assume for example, that there is a dimension named prodid that has three values, Prod01, Prod02, and Prod03, and you have a variable named quantity that is dimensioned by prodid. As the following code snippet illustrates, when you assign the value 3 to quantity, Oracle OLAP loops over the values in status for each dimension of the target and assigns the value 3 to all the cells in quantity.
```
quantity = 3
REPORT quantity

PRODID          QUANTITY
-------------- ----------
PROD01             3.00
PROD02             3.00
PROD03             3.00
```
Other OLAP DML statements (for example, REPORT, ROW, and FOR) also loop through all of the in status elements of a dimensioned object when they execute.

By default, looping statements loop through the values of a dimensioned object using the order in which the dimensions of the object are listed in the definition of the object. Also, when a variable is dimensioned by a composite, most looping statements loop through the variable as though it was not dimensioned by a composite, but was, instead, dimensioned by the base dimensions of the composite.

The OLAP DML provides ways for you to change the default looping behavior or to explicitly request looping:

ACROSS phrase—Some looping statements (such as SET that you use to assign values) have an ACROSS phrase that you can use to specify nondefault looping behavior. Using the ACROSS phrase, you can specify:
- The specific dimensions (and order) in which you want the statement to loop. In this case, the statement will loop over the dimensions in the order that you specify them in the ACROSS phrase, not in the order in which they appear in the variable's definition.
- A composite over which you want the statement to loop. When a variable is dimensioned by a composite, specifying the name of a composite improves performance. When you specify the name of a composite in the ACROSS phrase of a looping statement, the statement only loops over the existing cells of a variable.
For more complete documentation of the ACROSS phrase, see SET.
ACROSS statement—When an OLAP DML statement is not a looping statement or does not include an ACROSS phrase, you can request looping behavior by coding the DML statement as an argument of the ACROSS statement.