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 statement. 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: (Default) 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.
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

Results with SORTCOMPOSITE Set to NO

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

Example 21-45 Sorting on a Composite

In the following example, a variable called coupon_count holds the number of coupons that were redeemed for certain products in certain districts. coupon_count is dimensioned by a composite called coupon_composite, which holds the combinations of products and districts for which coupons were distributed.

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

Assume that you issue the following statements.

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

With SORTCOMPOSITE set to YES, and after the following LIMIT and SORT commands, the preceding REPORT statement produces the following report. Notice that the products are listed in descending order according to the total of Boston and Chicago figures for each product.

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

When SORTCOMPOSITE had been set to NO, Oracle OLAP would not necessarily have looped over the product dimension according to the sorted values of coupon_count. The looping order would have been the order that required the least processing effort from Oracle OLAP. If coupon_count had been a very large variable, the performance improvement might have been significant.