30 June, 2015

Oracle Materialized Views : Overview



  • Data warehousing is done; storing large quantities of data on daily or monthly basis.
  • for performance, summaries are used; spl aggregate views which calculate the complex joins and calculations and store the result before query execution and stores in db.

  • These summaries are objects; called MATERIALIZED VIEWS.
  • Materialized views are the equivalent to a summary.
  • End user-> Queries the db table-> server rewrites the query to use materiazed views-> makes it fast to run and good response time.
  • Users can directly use mat. views as well instead of the Oracle server doing that internally.
  • The query optimizer automatically knows how and when to use mat. views.


















  • optimizer checks which execution plan is better; the normal one or the one with the mat. view; and based on that; the best one is used.
  • it can have as many aggregate func and joins as you want!!!! Amazing...






Author's Advice