Thalasar Ventures

Dimensions are an optional feature associated with materialized views. The use of dimensions makes query rewrite much more flexible. A dimension is an Oracle object that stores hierarchical information about your data. This information is used by the optimizer as it determines whether it can rewrite your query to use a materialized view.

Data in a data warehouse typically represents various hierarchical structures. One example would be the time relationship of data, which might be of the form of decade, year, quarter, month, week, day, hour, and so on. These types of relationships generally are used in data warehouse queries to roll up or drill down to more or less detail as needed. Oracle has introduced an object called a dimension that allows you to define these relationships in tables. Oracle then uses these dimension relationship definitions to more effectively rewrite queries and to determine if a materialized view can best be used.

Although Oracle can rewrite queries without the presence of dimensions, the definition of dimensions makes the query rewrite process much more versatile. The use of dimensions allows the query rewrite to move beyond SQL checking of the query’s SELECT clause and its join conditions in the WHERE clause; with dimensions, the query rewrite can look at the summary grouping and also other operations.

When you create a dimension, you define the various components of the dimension and then the various hierarchies of the dimension. For example, if you created a table with columns for the number of landing cycles that an aircraft might go through one cycle represent a single landing, you might create a table called TAKEOFF_LAND, and in it, you might create columns for year, quarter, month, week, day, and column of takeoff. These columns would be components, or levels, of the hierarchy; the order in which they appear would constitute the hierarchy itself.

During database services of data warehousing, you need to take care of dimension object very carefully. You need to understand basic concept of dimension object. Data warehousing always contains so many schedule jobs which data extracting continuously and during this data loading, if dimension object would become invalid or corrupt then you would not able to recover because size of database always large for data warehousing. It is not easy to recovery using any backup recovery scenario. Due to these all reasons, you need to take care of all dimension objects in data warehousing about levels and hierarchy representation.

More Oracle Articles