1 Reply Latest reply: Jun 6, 2013 4:23 AM by 1013185 RSS

    Mareilaized View

    1009739
      What is the use of query rewrite in materialized view. What is does, When it will be useful.?
      What is difference between normal materialized view and query rewrite materialized view.?

      Thanks in advance
        • 1. Re: Materialized View
          1013185
          The idea behind query rewrite is very simple: sql tuning without the need to rewrite the sql statement. The easiest way to explain the feature is using a small example.

          Imagine a facts table with three columns, date, customer_id and the numeric revenue value. If you want to know the total revenue per month, you can read the daily values and sum them up. This will work fine, as long as the number of records is "ok" (regarding the size of the machine used). This query is implemented in an application, which you can't change. Say, the duration for the answer is fine, up to 10 million records, but now you have 100 million records stored in the table and the application is very slow.

          In such a scenario You have two ways to "tune" to application: Rewrite the statement (impossible, the application cannot be changed) or You use query rewrite. In this case, You create a MV, which stores the monthly sum (and count of rows aggregated). After enabling query rewrite, the optimizer of the database will use the MV to answer the query. Performance will increase and everyone is happy again.

          Well, that's at least the idea behind this DB-feature. The MV in the given example will be used by the database to answer other "questions" as well, e.g. "what is my average revenue per month". Just be sure to read the documentation concerning the required preferences for query rewrite enabled MV's and the usage of MV logs ...

          Edited by: 1010182 on 06.06.2013 02:23