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
Level_clauses
[Hierarchy_clauses]
[attribute_clasues];

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 plan_table.id
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.

SQL>

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.

Dropping DIMENSIONS

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".

��