ADVISE_PARTITIONING_LEVEL Function

The ADVISE_PARTITIONING_LEVEL function returns the level used by the Sparsity Advisor for partitioning over a dimension. It returns NULL if the Sparsity Advisor did not partition the cube, and raises an exception if the dimension hierarchy is not level-based.

Note:

You cannot execute this function from within the OLAP Worksheet. You must execute if in a SQL tool such as SQL*Plus.

See also:

"Using the Sparsity Advisor"

Syntax

ADVISE_PARTITIONING_LEVEL (
          cubename   IN  VARCHAR2,
          sources    IN  dbms_aw$_dimension_sources_t,
          advtable   IN  VARCHAR2 DEFAULT NULL)
     RETURN VARCHAR2;

Parameters

Table B-7 ADVISE_PARTITIONING_LEVEL Function Parameters

Parameter	Description
`cubename`	The same cubename value provided in the call to `ADVISE_SPARSITY`.
`sources`	The name of an object (such as a PL/SQL variable) defined with a data type of `DBMS_AW$_DIMENSION_SOURCES_T`, which was populated by `ADD_DIMENSION_SOURCE` for use by `ADVISE_SPARSITY`.
`advtable`	The name of a table created by the `SPARSITY_ADVICE_TABLE` procedure for storing the results of analysis.

Example

The following program fragment shows the ADVISE_PARTITIONING_LEVEL function being used to query the results after using the Sparsity Advisor.

DECLARE
     dimsources dbms_aw$_dimension_sources_t;
BEGIN
-- Calls to ADD_DIMENSION_SOURCE and ADVISE_SPARSITY omitted here
     .
     .
     .
dbms_output.put_line('Partitioning Level: ' || 
     dbms_aw.advise_partitioning_level('units_cube', dimsources,
    'aw_sparsity_advice'));
END;
/

The program uses DBMS_OUTPUT to display the partitioning level, which in this case is YEAR.

Partitioning Level: year