Invalid Dimension Objects - Query Performance in Data warehouse

We come across a situation where the query performance gets poor in a data warehouse environment. There are several reason for that. But I like to focus particularly on the impact from a dimension becoming invalid.

When a dimension becomes invalid, any query that could take an advantage of the dimension during the query rewrite process can no longer use it. This can lead to a slower query execution. It is well-known that the hierarchical/join/attribute relationship structure defined by dimensions can render a significant performance advantages.

As a result, it might be worth checking the validity of dimensions if your query run slower than it used to.

• Check if any dimension is invalid : A dimension becomes invalid when we alter/drop any of the objects that the dimension references.    SQL>   select invalid, dimension_name from dba_dimensions;
• Recompile the invalid dimension   SQL>  alter dimension cust_dim compile;

---------------------------------------------------------------------------------------------------------

We can view how a dimension has been defined using DBMS_DIMENSION.describe_dimension('cust_dim') procedure.

Also, we can verify the hierarchical/attribute/join relationships on all the rows that are defined by the dimension using  DBMS_DIMENSION.validate_dimension('sh.cust_dim', false, true)  procedure. Note that 'dimension_exception' table should be created before running this validate procedure because any error(bad row) that is identified during the validation process gets recorded in dimension_exception table.