SORTCOMPOSITE

The SORTCOMPOSITE option indicates whether Oracle OLAP should perform sorting on composite values when you issue a statement, such as REPORT, that explicitly loops over the composite. The sorting brings the composite values in line with the current order of the composite's base dimension values.

By default, SORTCOMPOSITE is set to YES, and Oracle OLAP performs the required sorting. You set SORTCOMPOSITE to NO only when you do not care how composite values are sorted and you want to save the processing time Oracle OLAP would have spent on the sorting.

SORTCOMPOSETE affects Oracle OLAP behavior only when you have explicitly specified that looping should occur over a composite, for example when you specify the composite name after a DOWN or ACROSS keyword in a REPORT command. Of course, when the composite has already been sorted according to the current order of its base dimensions values, Oracle OLAP does not unnecessarily sort the values again.

Syntax

SORTCOMPOSITE = {YES|NO}

Arguments

YES

In an explicitly specified loop over a composite, Oracle OLAP sorts the composite values according to the order of the composite's base dimension values (when they have not already been sorted in this way). The task of sorting requires some processing time, so when variables are large, performance can be affected. (Default)

NO

In an explicitly specified loop over a composite, Oracle OLAP does not sort the composite values according to the order of the composite's base dimension values. Eliminating this sorting step can improve Oracle OLAP performance, when large variables are involved. See "Results with SORTCOMPOSITE Set to NO".

Notes

When SORTCOMPOSITE is set to NO, the sort order of the composite value is undefined. It is the order that demands the least processing effort from Oracle OLAP, so it depends on the activities that have preceded the statement that requires the looping. The order will differ from session to session and from time to time within a session. It is not necessarily the default order for the values of the composite.

Examples

DEFINE coupon_composite COMPOSITE <product district>
 
DEFINE coupon_count VARIABLE  -
   INTEGER <month coupon_composite <product district>>

SORTCOMPOSITE = YES
LIMIT month TO FIRST 1
SORT product D TOTAL(coupon_count, product)
REPORT DOWN coupon_composite W 15 coupon_count

-COUPON_COUNT--
                      -----MONTH-----
 PRODUCT    DISTRICT       Jan95
---------- ---------- ---------------
Racquets   Boston                  93
Tents      Boston                  42
Canoes     Boston                  67
Sportswear Boston                  29
Racquets   Chicago                102
Tents      Chicago                 51