What is Dimensions Tables in Oracle?

Dimension objects are widely used in data warehousing database. Article explains characteristics of dimension object.
Spread the Word
Listed Under

Oracle Dimensions
Data Warehouse
Data Warehousing
Oracle Dba


Skokie - Illinois - US

March 22, 2012 - PRLog -- 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.


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.


Dbametrix is leading Oracle database service provider company and offering data warehousing administration. Remote DBA of Dbametrix are able to provide database support on all kind of performance tuning, Oracle troubleshooting, and any disaster recovery of small to very large databases.

# # #

Expert remote Oracle DBA team offers remote dba support,remote dba services,remote dba work,remote database monitoring,database services,outsourcing with very low cost plan using SLA.
Posted By:***@dbametrix.com Email Verified
Phone:91 9429389363
Tags:Oracle Dimensions, Data Warehouse, Data Warehousing, Databases, Oracle Dba
Industry:Business, Technology, Services
Location:Skokie - Illinois - United States
Account Email Address Verified     Account Phone Number Verified     Disclaimer     Report Abuse
Dbametrix Solutions PRs
Trending News
Top Daily News
Top Weekly News

Like PRLog?
Click to Share