For how many years have you been working
with physical servers that are starving your database of the memory
necessary to deploy important new performance features such as the Result
Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and
Full Database Caching? Too long? Contact me to learn how to improve all
queries ... not just some queries.
Purpose
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.
Dependencies
ALL_DIMENSIONS
CDB_DIM_LEVEL_KEY
DBA_DIM_LEVEL_KEY
CDB_DIMENSIONS
DBA_DIMENSIONS
DBMS_DIMENSIONS
CDB_DIM_CHILD_OF
DBA_DIM_CHILD_OF
DIM$
CDB_DIM_HIERARCHIES
DBA_DIM_HIERARCHIES
USER_DIMENSIONS
CDB_DIM_JOIN_KEY
DBA_DIM_JOIN_KEY
Reverse Engineering Dimension Source Code
conn sys@pdbdev as sysdba
desc dba_dimensions
SELECT *
FROM dba_dimensions;
desc dim$
-- this works
SELECT dbms_metadata.get_ddl('DIMENSION', 'TIMES_DIM', 'SH')
FROM dual;
SELECT d.dimtext
FROM obj$ o, dim$ d
WHERE o.obj# = d.obj#
AND o.name = 'TIMES_DIM';
-- this is clearer to read but you must turn it into DDL
set serveroutput on
exec dbms_dimension.describe_dimension('SH.TIMES_DIM');
System Privileges
ALTER ANY DIMENSION
CREATE DIMENSION
DROP ANY DIMENSION
CREATE ANY DIMENSION
Create Dimension
Dimension Creation with Attribute
CREATE DIMENSION [schema_name.]<dimension_name>
LEVEL <level_name> IS (<table_name.column_name>)
..
LEVEL <level_name> IS (<level_table_name.column_name>)
[SKIP WHEN NULL]
ATTRIBUTE <level_name> DETERMINES (<dependent_column_name_list>);
Dimension Creation with Extended Attribute Clause
CREATE DIMENSION [schema_name.]<dimension_name>
LEVEL <level_name> IS (<table_name.column_name>)
..
LEVEL <level_name> IS (<level_table_name.column_name>)
[SKIP WHEN NULL]
ATTRIBUTE <attribute_name> LEVEL <level_name> DETERMINES (<dependent_column_name_list>);
Dimension Creation with Hierarchy
CREATE DIMENSION [schema_name.]<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@pdbdev
CREATE DIMENSION test1_dim
LEVEL dim_demo1 IS (customers.cust_id);
CREATE DIMENSION dim_demo2
LEVEL city IS (customers.cust_city)
LEVEL state IS (customers.cust_state_province)
LEVEL country IS (countries.country_id)
HIERARCHY geog_rollup (customers.city
CHILD OF city
CHILD OF state
CHILD OF country);
Dimension Creation with Join
CREATE DIMENSION [schema_name.]<dimension_name>
LEVEL <level_name> IS (<table_name.column_name>)
JOIN ?
conn sh/sh@pdbdev
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 Attribute
ALTER DIMENSION <dimension_name>
DROP ATTRIBUTE <attribute_name>
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);
