| General |
Note:
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 |
dim$
| dbms_dimensions |
dba_dimensions |
all_dimensions |
user_deminsions |
|
Dimension Source Code |
desc dba_dimensions
SELECT DISTINCT dimension_name
FROM dba_dimensions;
desc dim$
set pagesize 30
set linesize 121
col dimtext format a120
set long 100000
SELECT DISTINCT dimension_name
FROM dba_dimensions
SELECT d.dimtext
FROM obj$ o, dim$ d
WHERE o.obj# = d.obj#
AND o.name = '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>); |
CREATE DIMENSION test_dim
LEVEL lat IS (servers.latitude)
LEVEL lon IS (servers.longitude)
HIERARCHY latlon_rollup (lon CHILD OF lat);
|
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);
|
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; |
