If you are using Oracle (10g upwards) database for data warehousing, chances are you might have come across DIMENSION in Oracle.
What is it and how does it help?
In data warehousing environment, often you find tables do not have referential integreties among them. While, I do not condone it, sometimes it is un-avoidable as source data comes from mainframe where, due to historic design, RDBMS like constraints could not be implemented.
So, when these data are loaded to Oracle tables (without any cleanup), implementing RDBMS constraints becomes difficult.
Without primary/foreign key relationships, Oracle optimizer will be confused as it won't know how to join different tables.
This is where Dimension comes to help!
Dimensions are simply logical constrains defined among tables. A product code may be logically related to transaction table which is defined by CREATE DIMENSION command.
What is the advantage? Well, it then behaves as if it knows foreign key relationships between tables (though not exactly the case).
The main performance gain is that it helps Oracle to rewrite queries so that it can either use a materialized view or avoid complex joins.
If you run a query which does something like this - show all possible aggregations from a very_large_table, it may take a very long time to complete.
Where as, if your DIMENSIONs are defined then Oracle may fetch same result by quering a smaller_table (which may be materialized view).
By defining Dimensions, you tell Oracle where else it may look for to satisfy your complex aggregation requirements.
There are some pre-requisites though, you must enable query rewrite enabled parameter to true beforehand.
What is it and how does it help?
In data warehousing environment, often you find tables do not have referential integreties among them. While, I do not condone it, sometimes it is un-avoidable as source data comes from mainframe where, due to historic design, RDBMS like constraints could not be implemented.
So, when these data are loaded to Oracle tables (without any cleanup), implementing RDBMS constraints becomes difficult.
Without primary/foreign key relationships, Oracle optimizer will be confused as it won't know how to join different tables.
This is where Dimension comes to help!
Dimensions are simply logical constrains defined among tables. A product code may be logically related to transaction table which is defined by CREATE DIMENSION command.
What is the advantage? Well, it then behaves as if it knows foreign key relationships between tables (though not exactly the case).
The main performance gain is that it helps Oracle to rewrite queries so that it can either use a materialized view or avoid complex joins.
If you run a query which does something like this - show all possible aggregations from a very_large_table, it may take a very long time to complete.
Where as, if your DIMENSIONs are defined then Oracle may fetch same result by quering a smaller_table (which may be materialized view).
By defining Dimensions, you tell Oracle where else it may look for to satisfy your complex aggregation requirements.
There are some pre-requisites though, you must enable query rewrite enabled parameter to true beforehand.
No comments:
Post a Comment