1

Recently, in enterprise's Oracle database, I have seen some materialized views that filter directly columns of table without adding aggregate fields and without calling custom functions.

Example:

create view VW_IMP_INVENTORY 
  as SELECT <some-columns> FROM <other-schema>.TB_INVENTORY;

create materialized view MV_IMP_INVENTORY 
  as SELECT * from VW_IMP_INVENTORY; 

In this situation, what are the advantages of using a MATERIALIZED VIEW instead of using directly the VIEW used in MATERIALIZED VIEW ?

  • Typically the materialized view will be faster than the view. However, by default the materialized view has to b e refreshed manually (or by scheduled task) – Wernfried Domscheit Nov 27 '18 at 12:56
  • @Wernfried - in the described situation, materialized view is not faster ! Why ? Because VIEW used don't make any row filtering. – schlebe Nov 27 '18 at 15:01
  • Yes, I missed that. The view makes just a plain select on a single table. Of course then it does not make any difference. I.e. I don't see any advantage of using materialized view. The only disadvantage is the data could be outdated if the MView is not refreshed. – Wernfried Domscheit Nov 27 '18 at 15:07
  • What is faster than repeatedly scanning a kind of static, wide table and throwing away most of the data from the unneeded columns which were read from the disk for no purpose at all? Scanning a narrow table (or materialized view) with only the columns needed. Or yes, it could be used as a partial snapshot of that table. To be honest, I have seen many other concepts that bothered me more. – Balazs Papp Nov 28 '18 at 22:13
  • 1
    Looks pointless to me. Don't discount the possibility that the person who created the MV didn't really understand what they were doing or how they though an MV was going to meet their goals -- whatever those now unknown goals might have been. – EdStevens Nov 29 '18 at 22:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.