| General Information |
| Description |
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 (called a 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 SQLAccess Advisor uses these relationships to recommend creation of specific materialized views.
A dimension defines hierarchical (parent/child) relationships between pairs of columns or column sets.
Each value at the child level is associated with one and only one value at the parent level.
A hierarchical relationship is a functional dependency from one level of a hierarchy to the next level in the hierarchy.
A dimension is a container of logical relationships between columns, and it does not have any data storage assigned to it. |
| Dimension Related Data Dictionary Objects |
| all_dimensions |
dba_dim_hierarchies |
dim$ |
| dba_dimensions |
dba_dim_join_key |
dbms_dimensions |
| dba_dim_child_of |
dba_dim_level_key |
user_deminsions |
|
| Reverse Engineering Dimension Source Code |
conn / as sysdba
desc dba_dimensions
SELECT *
FROM dba_dimensions;
desc dim$
SELECT d.dimtext
FROM obj$ o, dim$ d
WHERE o.obj# = d.obj#
AND o.name = 'TIMES_DIM';
-- this will not work
exec dbms_dimension.describe_dimension('SH.TIMES_DIM');
set serveroutput on
exec dbms_dimension.describe_dimension('SH.TIMES_DIM'); |
| System Privileges |
| alter any dimension |
create any dimension |
| create dimension |
drop any dimension |
|
| |
| Create Dimension |
| Dimension Creation with Hierarchy |
CREATE DIMENSION <dimension_name>
LEVEL <level_name> IS (<table_name.column_name>)
..
LEVEL <level_name> IS (<table_name.column_name>)
[SKIP WHEN NULL]
HIERARCHY <heirarchy_name> (<child_level CHILD OF <parent_level>); |
conn sh/sh
GRANT select ON customers TO uwclass;
GRANT select ON countries TO uwclass;
conn uwclass/uwclass
CREATE TABLE customers AS SELECT * FROM sh.customers;
SELECT COUNT(*) FROM customers;
CREATE TABLE countries AS SELECT * FROM sh.countries;
SELECT COUNT(*) FROM countries;
CREATE DIMENSION test1_dim
LEVEL customer_demo IS (customers.cust_id);
-- this will not work
desc test1_dim
set serveroutput on
exec dbms_dimension.describe_dimension('TEST1_DIM'); |
| Dimension Creation with Join |
CREATE DIMENSION <dimension_name>
LEVEL <level_name> IS (<table_name.column_name>)
JOIN ? |
CREATE DIMENSION test_dim
LEVEL e IS (servers.latitude)
HIERARCHY latlon_rollup (lon CHILD OF lat);
HIERARCHY latlon_rollup (lon CHILD OF lat)
*
ERROR at line 3:
ORA-30342: referenced level is not defined in this dimension |
| Dimension Creation with Attribute |
CREATE DIMENSION <dimension_name>
LEVEL <level_name> IS (<table_name.column_name>)
..
LEVEL <level_name> IS (<table_name.column_name>)
[SKIP WHEN NULL]
HIERARCHY <heirarchy_name> (<child_level
CHILD OF <parent_level>
..
CHILD OF <parent_level>
JOIN KEY (child_key_column) REFERENCES <parent_level>);
ATTRIBUTE ... |
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); |
| |
| Alter Dimension |
| Alter Dimension Add Attribute Clause |
ALTER DIMENSION <dimension_name>
ADD <attribute_clause> |
| ALTER DIMENSION customers_dim |
| Alter Dimension Add Dimension Join Clause |
ALTER DIMENSION <dimension_name>
ADD <attribute_clause> |
| ALTER DIMENSION customers_dim |
| Alter Dimension Add Extended Attribute Clause |
ALTER DIMENSION <dimension_name>
ADD <attribute_clause> |
| ALTER DIMENSION customers_dim |
| Alter Dimension Add Hierarchy Clause |
ALTER DIMENSION <dimension_name>
ADD <hierarchy_clause> |
| ALTER DIMENSION customers_dim |
| Alter Dimension Add Level Clause |
ALTER DIMENSION <dimension_name>
ADD <level_clause> |
ALTER DIMENSION customers_dim
|
| Alter Dimension Drop |
|
ALTER DIMENSION customers_dim
DROP ATTRIBUTE country; |
| Alter Drop With Cascade Option |
|
| |
| Alter Drop With Restrict Option |
|
EXPLAIN PLAN FOR
SELECT last_name, employee_id, manager_id, LEVEL
FROM emp
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;
SELECT * FROM TABLE(dbms_xplan.display);
CREATE DIMENSION emp_dim
LEVEL empid IS (emp.employee_id)
LEVEL mgrid IS (emp.manager_id)
HIERARCHY emp_rollup (mgrid CHILD OF empid); |
| |
| Drop Dimension |
| Drop Dimension |
DROP DIMENSION <dimension_name>; |
| DROP DIMENSION test_dim; |
