Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
ALTER CLUSTER to ALTER SEQUENCE, 4 of 18
Use the ALTER
DIMENSION
statement to change the hierarchical relationships or dimension attributes of a dimension.
The dimension must be in your schema or you must have the ALTER
ANY
DIMENSION
system privilege to use this statement.
A dimension is always altered under the rights of the owner.
alter_dimension::=
alter_dimension
level_clause::=
level_clause
hierarchy_clause::=
hierarchy_clause
join_clause::=
join_clause
attribute_clause::=
attribute_clause
The following keywords and parameters have meaning unique to ALTER
DIMENSION
. The remaining keywords and parameters have the same functionality that they have in the CREATE
DIMENSION
statement.
Specify the schema of the dimension you want to modify. If you do not specify schema
, Oracle assumes the dimension is in your own schema.
Specify the name of the dimension. This dimension must already exist.
The ADD
clauses let you add a level, hierarchy, or attribute to the dimension. Adding one of these elements does not invalidate any existing materialized view.
Oracle processes ADD
LEVEL
clauses prior to any other ADD
clauses.
The DROP
clauses let you drop a level, hierarchy, or attribute from the dimension. Any level, hierarchy, or attribute you specify must already exist.
Restriction: If any attributes or hierarchies reference a level, you cannot drop the level until you either drop all the referencing attributes and hierarchies or specify CASCADE
.
Specify CASCADE
if you want Oracle to drop any attributes or hierarchies that reference the level, along with the level itself.
Specify RESTRICT
if you want to prevent Oracle from dropping a level that is referenced by any attributes or hierarchies. This is the default.
Specify COMPILE
to explicitly recompile an invalidated dimension. Oracle automatically compiles a dimension when you issue an ADD
clause or DROP
clause. However, if you alter an object referenced by the dimension (for example, if you drop and then re-create a table referenced in the dimension), the dimension will be invalidated, and you must recompile it explicitly.
The following examples modify the customers_dim
dimension in the demo schema sh
:
ALTER DIMENSION customers_dim DROP ATTRIBUTE country; ALTER DIMENSION customers_dim ADD LEVEL zone IS customers.cust_postal_code ADD ATTRIBUTE zone DETERMINES (cust_city);
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|