remote dba support
More
    HomeSql TutorialMaterialized 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 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.

    When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.

    This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

    - Advertisement -
    dbametrix
    - Advertisment -
    remote dba services

    Most Popular