Materialized Views

By arunraj

In this article let’s discuss about using Materialized views in Oracle.

What is Materialized View?

Materialized view is similar to a view but the actual table data will exist in the Database. These views will be refreshed automatically when the master tables are updated.

CREATE MATERIALIZED VIEW mv_employee
REFRESH COMPLETE START WITH SYSDATE
AS SELECT EMP_ID,EMP_NAME,DEPT_ID,MAX(SALARY) FROM EMPLOYEES@orcl GROUP BY EMP_ID,EMP_NAME,DEPT_ID;

The above create statement creates a materialized view for the given query with complete refresh rate.

There are three refresh rates for Materialized view.

COMPLETE - it refreshes the entire table (or query)
FAST - it refreshes only the updated (UPDATE/DELETE/INSERT) changes.
FORCE - The system will try for FAST refresh, if it is not possible then it will go with COMPLETE refresh.

Mostly Materialized views are created to have a snapshot of a table or view (select query) from a remote database. Since it stores the physical data, it can increase the query running time or performance compared to normal views.

In this way we can use materialized views in oracle. Materialized views are available for Oracle version 9.0.

Technology:

Oracle

Search

Disclaimer Notice

THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS OF TECHDIVE.IN ''AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS OF TECHDIVE.IN BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. ANY COPYRIGHT INFRINGEMENT ON THE ARTICLES/CONTENT POSTED IN TECHDIVE.IN IS PURELY UN-INTENTIONAL AND NO LIABILITY CAN BE MADE AGAINST TECHDIVE.IN OR ITS CONTRIBUTORS.

The trademarks like Java, Spring, Spring Framework, Spring Web Flow, Oracle, SNMP, Shell Scripting, Log4j and other trademarks are property of their respective owners.

Please read our terms of use before using the site.