CREATE HIERARCHY

Purpose

Use the CREATE HIERARCHY statement to create a hierarchy. A hierarchy specifies the hierarchical relationships among the levels of an attribute dimension.

Tip:

You can view and run SQL scripts that create hierarchies at the Oracle Live SQL website at https://livesql.oracle.com/apex/livesql/file/index.html. The website has scripts and tutorials that demonstrate the creation and use of analytic views.

Prerequisites

To create a hierarchy in your own schema, you must have the CREATE HIERARCHY system privilege. To create a hierarchy in another user's schema, you must have the CREATE ANY HIERARCHY system privilege.

Syntax

create_hierarchy::=

Description of the illustration create_hierarchy.eps

sharing_clause::=

Description of the illustration sharing_clause.eps

classification_clause::=

Description of the illustration classification_clause.eps

hier_using_clause::=

Description of the illustration hier_using_clause.eps

level_hier_clause::=

Description of the illustration level_hier_clause.eps

hier_attrs_clause::=

Description of the illustration hier_attrs_clause.eps

hier_attr_clause::=

Description of the illustration hier_attr_clause.eps

hier_attr_name::=

Description of the illustration hier_attr_name.eps

Semantics

OR REPLACE

Specify OR REPLACE to replace an existing definition of a hierarchy with a different definition.

FORCE and NOFORCE

Specify FORCE to force the creation of the hierarchy even if it does not successfully compile. If you specify NOFORCE, then the hierarchy must compile successfully, otherwise an error occurs. The default is NOFORCE.

schema

Specify the schema in which to create the hierarchy. If you do not specify a schema, then Oracle Database creates the hierarchy in your own schema.

hierarchy

Specify a name for the hierarchy.

sharing_clause

Specify whether to create the hierarchy as an application common object. Specifying METADATA shares the hierarchy's metadata, but its data is unique to each container. Specifying DATA shares the hierarchy object; its data is the same for all containers in the application container and the data is stored only in the application root. Specifying NONE excludes the hierarchy from being shared.

classification_clause

Use the classification clause to specify values for the CAPTION or DESCRIPTION classifications and to specify user-defined classifications. Classifications provide descriptive metadata that applications may use to provide information about analytic views and their components.

You may specify any number of classifications for the same object. A classification can have a maximum length of 4000 bytes.

For the CAPTION and DESCRIPTION classifications, you may use the DDL shortcuts CAPTION 'caption' and DESCRIPTION 'description' or the full classification syntax.

You may vary the classification values by language. To specify a language for the CAPTION or DESCRIPTION classification, you must use the full syntax. If you do not specify a language, then the language value for the classification is NULL. The language value must either be NULL or a valid NLS_LANGUAGE value.

hier_using_clause

Specify the attribute dimension that has the members of the hierarchy.

level_hier_clause

Specify the organization of the hierarchy levels.

hier_attrs_clause

Specify classifications that contain descriptive metadata for the hierarchical attributes. A hier_attr_clause for a given hier_attr_name may appear only once in the list.

All hierarchies always contain all of the hierarchical attributes, but a hierarchical attribute does not have descriptive metadata associated with it unless you specify it with this clause.

hier_attr_clause

Specify a hierarchical attribute and provide one or more classifications for it.

hier_attr_name

Specify a hierarchical attribute.

Examples

The following example creates the TIME_HIER hierarchy:

CREATE OR REPLACE HIERARCHY time_hier  -- Hierarchy name
USING time_attr_dim               -- Refers to TIME_ATTR_DIM attribute dimension
 (month CHILD OF                  -- Months in the attribute dimension
 quarter CHILD OF
 year);

The following example creates the PRODUCT_HIER hierarchy:

CREATE OR REPLACE HIERARCHY product_hier
USING product_attr_dim
 (category
  CHILD OF department);

The following example creates the GEOGRAPHY_HIER hierarchy:

CREATE OR REPLACE HIERARCHY geography_hier
USING geography_attr_dim
 (state_province
  CHILD OF country
  CHILD OF region);