Oracle can use computed aggregrates and joins from materialized views to improve performance on a database query by rewriting a query against the warehouse to make use of the view. To allow this to happen the view must support query rewrite and the warehouse itself must support query rewrite. Be aware that the materialized view must be fresh, or the related integrity session parameters relaxed to allow the rewrite to happen.
To enable query rewrite on the view do
CREATE MATERIALIZED VIEW my_view_mv
ENABLE QUERY REWRITE AS
SELECT .....
To enable query rewrite ensure QUERY_REWRITE_ENABLED = TRUE for the session or the system.
Basic query rewrite documentation for Oracle 11g R2
http://docs.oracle.com/cd/E11882_01/server.112/e25554/qrbasic.htm
Advanced query rewrite documentation for Oracle 11g R2
http://docs.oracle.com/cd/E11882_01/server.112/e25554/qradv.htm
Make sure the warehouse supports the creation of the materialized view -
http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm
http://docs.oracle.com/cd/E11882_01/server.112/e25554/advmv.htm
No comments:
Post a Comment