CLEAR

The CLEAR command deletes the data that you specify for one or more variables.

Syntax

CLEAR [STATUS | {ALL [CACHE]}] [VALUES | {aggdata [USING aggmapname]}] -

FROM {varname [ ( PARTITION partition-name ) ] } [, ... ]

where aggdata is one or more of the following keywords that identifies the type of aggregated data that you want deleted from the variable.

AGGREGATES

LEAVES

PRECOMPUTES

NONPRECOMPUTES

Parameters

STATUS

Specifies that only the data that is currently in status is taken into consideration. (Default)

Tip:

When clearing a compressed composite, do not execute a CLEAR when only some values are in status.

ALL

Specifies that the command consider all of a variable's data regardless of the current status. Required when you specify either the CACHE or AGGREGATES keywords.

CACHE

Empties the session cache. When you specify this keyword, you must also specify the ALL keyword.

VALUES

(Default) Deletes all of a variable's stored data and replaces each deleted data value with an NA value.

AGGREGATES

Deletes the data in all cells populated by the execution of an AGGREGATE command or an AGGREGATE function. When you specify this keyword, you must also specify the ALL keyword.

PRECOMPUTES

For all variables except those dimensioned by a compressed composite, deletes any data that was calculated when an AGGREGATE command executed and replaces that data with NA values.

NONPRECOMPUTES

Deletes any data that was calculated on the fly when an AGGREGATE function executed and replaces that data with NA values.

LEAVES

Deletes the detail-level data, meaning, the "leaf" data.

Note:

You cannot specify this keyword for a variable dimension by a compressed composite.

varname

The name of a variable from which data is deleted.

aggmapname

The name of the aggmap that should be used.

You must include this phrase to clear a variable that is not a compressed composite or that does not have an $AGGMAP property. You do not have to specify this phrase to clear:

A variable that is dimensioned by a compressed composite. By default, CLEAR uses the structure of the compressed composite to clear the variable.
A variable that has an $AGGMAP property when you want CLEAR to use the aggmap specified by that property. If you do not specify a USING phrase for a variable that has an $AGGMAP property, then CLEAR uses the aggmap specified by that property.

When you include this phrase for a dimensioned aggmap, the dimensionality of every variable included in the CLEAR command must be identical to the dimensionality of the aggmap. In other words, every variable definition must have the same dimensions in the same order as those in the definition of the aggmap.

PARTITION partition_name

For a partitioned variable, specifies the name of a partition from which you want to clear data.

Note:

Clearing only a single partition of a compressed composite is resource intensive and time consuming as the variable is decompressed during the process.

Examples

Example 9-65 Clearing a Variable's Data

The CLEAR command gives you an easy way to delete all of a variable's stored data. Suppose you have defined a sales variable and loaded data into it. You then find out that much of this data has changed. It is more efficient to clear the sales variable and reload all of the data than it would be to change the existing data. You can do so with the following statement.

CLEAR ALL FROM sales

In this example, the VALUES keyword is assumed by default. Therefore, all of the sales data is deleted and replaced with NA values.

Example 9-66 Clearing Aggregated Data

Suppose you have aggregated data for your sales and units variable, and you have specified that all other data should be calculated on the fly.

The sales and units variables are defined with the same dimensions in the same order: time, product, and geography. Therefore, they have been aggregated with the sales.agg aggmap, which has the following definition.

DEFINE sales.agg AGGMAP <time, product, geography>

The sales.agg aggmap has the following contents.

RELATION time.r PRECOMPUTES (time ne 'YEAR99')
RELATION product.r PRECOMPUTES (product ne 'ALL')
RELATION geography.r

After aggregating both sales and units, you learn that there are certain geographic regions that none of your users access. Because geography is the slowest-varying dimension, you can probably reduce the number of pages needed to store data by deleting data for the geographic regions that no one needs which can reduce the size of your analytic workspace and possibly improve performance.

Set the status for each dimension. The only geographic regions that users need are New England, Europe, and Australasia. The following statements put all time periods and all products for every geographic region in the current status, except for the geographic regions that users need. In other words, the following statements put all of the data that users do not have to access in status.
```
LIMIT time TO ALL
LIMIT product TO ALL
LIMIT geography COMPLEMENT 'NewEngland' 'Europe' 'Australasia'
```

Use the following statement to delete the unneeded data.

CLEAR STATUS PRECOMPUTES FROM sales units USING sales.agg

Example 9-67 Clearing Cached Data

Data is cached when an aggmap specifies calculation on the fly and contains a CACHE SESSION statement.

For example, suppose the sales.agg aggmap has the following contents.

RELATION time.r PRECOMPUTES (time ne 'YEAR99')
RELATION product.r PRECOMPUTES (product ne 'ALL')
RELATION geography.r
CACHE SESSION

Note that the sales.agg contains a CACHE SESSION command. Consequently, Oracle OLAP calculates some data at the time a user requests it, and then stores it in the session cache. To clear this data from the sales variable, use the following statement.

CLEAR ALL CACHE FROM sales