Sunday, September 20, 2020
dbametrix
More
    Home Sql Tutorial Materialized views

    Materialized views

    The blog post provides information about how to create materialized view and how to enable and disable query rewrite functionality.

    Obviously, if a view uses many base tables linked during a complex way, which view goes to be used frequently, it’ll be very convenient to define it as a materialized view. this may greatly improve the performance of the database, because the view’s base SQL statement will only be executed once.

    On the opposite hand, there’s the disadvantage that if the materialized view or materialized view goes to possess to be reused within the future, then we’ll need a mechanism to update or refresh said materialized view, since the bottom tables of the view may have undergone modifications since creating it.

    - Advertisement -
    dbametrix

    For all this, when determining whether a view should be defined as a view or if it’s better to define it as a materialized view, we must assess the prices of getting to execute the bottom SQL statement of a traditional view whenever said view is accessed, versus the prices of storing and updating a materialized view.

    Syntax of the SQL command used to create materialized views

    CREATE MATERIALIZED VIEW view_name
    [TABLESPACE ts_name]
    [PARALELL (DEGREE n)]
    [BUILD {IMMEDIATE | DEFERRED}]
    [REFRESH {FAST | COMPLETE | FORCE | NEVER | ON COMMIT}]
    [{ENABLE | DISABLE} QUERY REWRITE]
    AS SELECT ... FROM ... WHERE ...

    The default values ​​for the various options are underlined.

    - Advertisement -
    dbametrix

    If the BUILD IMMEDIATE option is chosen, then the table associated with the materialized view is populated with data at the time of the execution of the SQL CREATE command. In contrast, if BUILD DEFERRED is used, the CREATE command will create only the view structure, but the associated physical table will not be populated with data until the first refresh or update of the materialized view is performed.

    The REFRESH option allows you to indicate the mechanism that the database will use to refresh or update the materialized view. The different mechanisms and the way in which a materialized view can be refreshed, will be the subject of another article in this blog. As a preview I will say that a complete or COMPLETE refresh means that the table associated with the materialized view is completely deleted, reinserting all the records returned by the execution of the view’s base SQL statement, and that a fast or FAST refresh, it means that the materialized view is updated only according to the changes made to the base tables of the view since the last refresh. In order to use the fast or FAST refresh, the logs of the materialized view must be previously created using the CREATE MATERIALIZED VIEW LOG command.

    The ENABLE / DISABLE QUERY REWRITE option determines whether or not the Oracle optimizer can rewrite the SQL statements so that, if possible, the materialized view is used in the execution phase instead of the base tables of the view included in the statement Original SQL. This is certainly a complex topic and one that will be the subject of a full article on this blog. As a preview, I will indicate that SQL statement rewriting is only available when using the cost-based Oracle optimizer.

    Consider Reading to these articles:

    - Advertisement -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    ORA-01194: file 1 needs more recovery to be consistent

    The blog post explains how to restore and recover database using until cancel with error ORA-01194

    How to enable Archivelog

    This blog post explains how to enable archive log mode in Oracle database for newest versions.

    Shared vs Static Library Performance

    The article explains the benefits of a shared library and static library usage in application building for improving application performance.

    Migration methods of Oracle Database

    Blog post explains which tricks and methods are simple to perform migration of small and large Oracle database

    Recent Comments