Monday, November 30, 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

    Oracle Scheduler Jobs Example

    Blog post explains how to configure job scheduler in Oracle by example with how to manage, delete, drop, disable and enable jobs with how to check history of job if broken and enable.

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    Recent Comments