 |
|
Oracle Tips by Burleson |
Oracle 10g Drop Dimension Components
Dropping dimension attribute columns used to be a
bit cumbersome, especially if the columns had multiple attributes.
Dropping one of the attributes was not possible without affecting
the other attributes.
To illustrate further, the 'attribute' clause
of the 'create dimension' statement allows the user to specify one
or multiple columns that are uniquely determined by a hierarchy
level. As seen in the example below,
CREATE
DIMENSION product_dim
prod_id IS (products.prod_id)
ATTRIBUTE prod_id DETERMINES prod_name, prod_status;
Give a working example- LEVEL clause is missing
and you should add parenthesis
The hierarchy level prod_id determines the
attributes prod_name and prod_status. In order to drop one of the
attributes, we had to drop the hierarchy level with all the
attributes.
Oracle 10g introduces a new syntax for both
create dimension and alter dimensions statements. It allows you to
give a general name to your attribute, and further specify multiple
attribute level clauses.
Let us look at an example,
CREATE
DIMENSION product_dim
LEVEL prod_id IS (products.prod_id)
ATTRIBUTE att
LEVEL prod_id DETERMINES prod_name, prod_status; parenthesis are
missing
In order to drop one of the attribute columns,
use the statement,
ALTER
DIMENSION product_dim
DROP ATTRIBUTE att LEVEL prod_id COLUMN prod_status;
As seen above, when you want to drop column
prod_status, keeping the prod_name column, you can use the new
syntax to facilitate and ease administration.
Get the complete Oracle10g story:
To get the code instantly, click here:
Need an Oracle Mentor?
BEI is now offering personal mentors for Oracle DBAs where you can have an
Oracle expert right at your fingertips, anytime day or night. We work with
hundreds of Oracle databases every year, so we know exactly how to quickly
assist you with any Oracle question.
Why risk an unplanned outage? You can now get telephone access to Don
Burleson or any of his Oracle Certified DBAs with more than 20 years of
full-time IT experience. Click here for details:
http://www.dba-oracle.com/service_oracle_backup.htm
|
