Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Administration of DIMENSION Objects

Oracle Database Tips by Donald Burleson

DIMENSION objects are used in data warehouse, DSS, and datamart-type applications to provide information on how denormalized tables relate to themselves. The CREATE DIMENSION command specifies level and hierarchy information for a table or set of related tables. If you want to use query rewrite with the Oracle optimizer and materialized views, you must specify dimensions that the optimizer then uses to ?decrypt? the inter- and intratable levels and hierarchies. As an administrator, you should know how these DIMENSION objects are created, altered, and dropped. To that end, we will discuss these topics and show some simple examples later in this chapter. For information beyond the basics, I suggest reviewing the application development and cartridge developer manuals.

Creation of DIMENSION Objects

The CREATE DIMENSION command is used to create dimensions in Oracle8i and Oracle9i The CREATE DIMENSION clause has the following syntax:

CREATE [FORCE|NOFORCE] DIMENSION [schema.]dimension_name

For an example of the use of the DIMENSION command, we?ll use the PLAN_TABLE implemented by EXPLAIN PLAN, which contains the recursive relationship between ID and PARENT_ID columns.

SQL> desc plan_table

 Name                            Null?    Type
-------------------------------  -------  ------------
 STATEMENT_ID                             VARCHAR2(30)
 TIMESTAMP                                DATE
 REMARKS                                  VARCHAR2(80)
 OPERATION                                VARCHAR2(30)
 OPTIONS                                  VARCHAR2(30)
 OBJECT_NODE                              VARCHAR2(128)

OBJECT_OWNER                             VARCHAR2(30)
 OBJECT_NAME                              VARCHAR2(30)
 OBJECT_INSTANCE                          NUMBER(38)
 OBJECT_TYPE                              VARCHAR2(30)
 OPTIMIZER                                VARCHAR2(255)
 SEARCH_COLUMNS                           NUMBER
 ID                                       NUMBER(38)
 PARENT_ID                                NUMBER(38)
 POSITION                                 NUMBER(38)
 COST                                     NUMBER(38)
 CARDINALITY                              NUMBER(38)
 BYTES                                    NUMBER(38)
 OTHER_TAG                                VARCHAR2(255)
 PARTITION_START                          VARCHAR2(255)

 PARTITION_STOP                           VARCHAR2(255)
 PARTITION_ID                             NUMBER(38)
 OTHER                                    LONG

SQL> create dimension test_dim

  2  level child_id is
  3  level parent_id is plan_table.parent_id
  4  hierarchy plan (child_id child of parent_ID)
  5  attribute parent_id determines plan_table.statement_id
  6  /

Dimension created.


With the dimension test_dim, if we now created a materialized view on the PLAN_TABLE, any queries attempting to do a ROLLUP or CUBE type operation across the ID-PARENT_ID levels would use the connection information stored in the DIMENSION to rewrite the query. The CREATE DIMENSION command also allows forcing of the creation if the tables don't exist or you don't have permission on them; it also allows join conditions to be specified between child and parent levels.

Oracle does not automatically validate the relationships you declare when creating a dimension. To validate the relationships specified in the hierarchy_clause and the join_clause of CREATE DIMENSION, you must run the DBMS_OLAP.validate_dimension procedure.  

Altering DIMENSION Objects

The ALTER DIMENSION command is used to add or drop LEVEL, HIERARCHY, or ATTRIBUTE information for a DIMENSION, as well as to force a compile of the object. An example would be if the PLAN_TABLE in the CREATE example didn't exist and we had used the FORCE keyword in the command. The views DBA_DIMENSIONS, ALL_DIMENSIONS, and USER_DIMENSIONS are used to monitor DIMENSION status; the INVALID (shown as ?I in the example below) tells the state of the DIMENSION, either Y for an INVALID DIMENSION or N for a VALID DIMENSION.

SQL> select * from user_dimensions;

OWNER                          DIMENSION_NAME             I   REVISION
?????????????????????????????? ?????????????????????????? - ??????????
SYSTEM                         TEST_DIM                   Y          1

SQL> @d:\orant81\rdbms\admin\utlxplan

Table created.

SQL> alter dimension test_dim compile;

Dimension altered.

SQL> select * from user_dimensions;

OWNER                          DIMENSION_NAME             I   REVISION
?????????????????????????????? ?????????????????????????? - ??????????
SYSTEM                         TEST_DIM                   N          1

As noted above, we could also have added or removed levels, hierarchies, or attributes using the ALTER DIMENSION command.


A DIMENSION object is dropped using the DROP DIMENSION command. The syntax of the DROP command is:

DROP DIMENSION [schema.]dimension_name;

An example is:

SQL> DROP DIMENSION system.test_dim;

Dimension dropped.

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.