|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
SQL Statements: CREATE CLUSTER to CREATE JAVA, 7 of 12
DIMENSION statement to create a dimension. A dimension defines a parent-child relationship between pairs of column sets, where all the columns of a column set must come from the same table. However, columns in one column set (or "level") can come from a different table than columns in another set. The optimizer uses these relationships with materialized views to perform query rewrite. The Summary Advisor uses these relationships to recommend creation of specific materialized views.
Oracle does not automatically validate the relationships you declare when creating a dimension. To validate the relationships specified in the
To create a dimension in your own schema, you must have the
DIMENSION system privilege. To create a dimension in another user's schema, you must have the
DIMENSION system privilege. In either case, you must have the
SELECT object privilege on any objects referenced in the dimension.
Specify the schema in which the dimension will be created. If you do not specify
schema, Oracle creates the dimension in your own schema.
Specify the name of the dimension. The name must be unique within its schema.
level_clause defines a level in the dimension. A level defines dimension hierarchies and attributes.
Specify the name of the level
Specify the columns in the level. You can specify up to 32 columns. The tables you specify in this clause must already exist.
Restrictions on level columns:
level_columnmust be non-null. (However, these columns need not have
hierarchy_clause defines a linear hierarchy of levels in the dimension. Each hierarchy forms a chain of parent-child relationships among the levels in the dimension. Hierarchies in a dimension are independent of each other. They may (but need not) have columns in common.
Each level in the dimension should be specified at most once in this clause, and each level must already have been named in the
Specify the name of the hierarchy. This name must be unique in the dimension.
Specify the name of a level that has an n:1 relationship with a parent level: the
child_level cannot be null, and each
child_level value uniquely determines the value of the next named
If the child
level_table is different from the parent
level_table, you must specify a join relationship between them in the
Specify the name of a level.
join_clause lets you specify an inner equijoin relationship for a dimension whose columns are contained in multiple tables. This clause is required and permitted only when the columns specified in the hierarchy are not all in the same table.
Specify one or more columns that are join-compatible with columns in the parent level.
If you do not specify the schema and table of each
child_column, the schema and table are inferred from the
OF relationship in the
hierarchy_clause. If you do specify the schema and column of a
child_key_column, the schema and table must match the schema and table of columns in the child of
parent_level in the
Specify the name of a level.
Restrictions on the join_clause:
join_clausefor a given pair of levels in the same hierarchy.
child_key_columnsmust be non-null and the parent key must be unique and non-null. You need not define constraints to enforce these conditions, but queries may return incorrect results if these conditions are not true.
child_key_columnscannot be in the same table as
parent_level, and the columns must be joinable.
attribute_clause lets you specify the columns that are uniquely determined by a hierarchy level. The columns in
level must all come from the same table as the
dependent_columns need not have been specified in the
For example, if the hierarchy levels are
city might determine
state might determine
country might determine
This statement was used to create the
customers_dim dimension in the sample schema
CREATE DIMENSION customers_dim LEVEL customer IS (customers.cust_id) LEVEL city IS (customers.cust_city) LEVEL state IS (customers.cust_state_province) LEVEL country IS (countries.country_id) LEVEL subregion IS (countries.country_subregion) LEVEL region IS (countries.country_region) HIERARCHY geog_rollup ( customer CHILD OF city CHILD OF state CHILD OF country CHILD OF subregion CHILD OF region JOIN KEY (customers.country_id) REFERENCES country ) ATTRIBUTE customer DETERMINES (cust_first_name, cust_last_name, cust_gender, cust_marital_status, cust_year_of_birth, cust_income_level, cust_credit_limit) ATTRIBUTE country DETERMINES (countries.country_name) ;