Minnu's Blog on Informatica & Data warehouse concepts


Wednesday, June 25, 2008

Materialized Views, Advantages, Options

Materialized views (Mview) are very much like a regular Oracle table, except that they are based on one or more tables. They can be simple or complex, read only, or updatable. Because an Mview is a physical table, changes are managed by updating the effected rows in the Mview when the underlying tables change. Updating an Mview is called refreshing it.
There are two types of refresh: fast and full. In a full refresh, the Mview is truncated and rebuild from the underlying tables. In a fast refresh, only the changes are updated in the Mview. A forced refresh tells the Mview to try and execute a fast refresh, and if that fails, execute a full refresh. In order to execute a fast refresh, the underlying tables must have a materialized view log. This log records changes to the table so that the Mview can retrieve them.

Mviews are normally used to aggregate information in a data warehouse database. They can also be used in replicating data from one database to another. Unlike normal views, INSERTs/UPDATEs/DELETESs of Mview require implementing advanced replication, which is far beyond the scope of this book. The Mview can be writable, which allows the data to be modified; however, the changes are not propagated back to the underlying tables and are lost when the Mview refreshes.

We are going to create an Mview similar to the normal view we already created. The query we will use is below.

select store_name, avg(quantity) qtfrom store join sales using (store_key)group by store_name;
Now, we create the materialized view .
SQL> create materialized view avg_sales2
2 refresh complete
3 NEXT sysdate + 5/(24*60)
4 as
5 select
6 store_name,
7 avg(quantity) qty
8 from
9 store join sales using (store_key)
10 group by store_name;
Materialized view created.

Because our Mview is complex (group by), we have to execute a complete refresh. Notice that the NEXT clause tells the Mview to refresh every five minutes. We can make a simple materialized view that allows fast refresh.

We want to be able to execute a fast refresh, so we need Mview logs on the underlying table, SALES.

SQL> create materialized view log on sales;
Materialized view log created.

Now, let’s make create an Mview that will limit access to data in the SALES table.

SQL> create materialized view sales_s110_v2
2 refresh fast
3 next sysdate + 1/(24*60)
4 as
5 select * from sales
6 where store_key = 'S110';
Materialized view created.

The above Mview will refresh fast using the Mview log on the SALES table and will refresh every minute. It will never be more than a minute behind the underlying table. Alternately, you can refresh on COMMIT. When changes are committed to the underlying tables, the Mview will execute a fast refresh.

Another advantage of using a materialized view is the ability to have the optimizer rewrite appropriate queries to use the materialized view instead of the underlying table. The query optimizer can use materialized views by automatically recognizing when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view and not to the underlying tables, which should result in a significant performance gain. To take advantage of this capability, a DBA must grant the user some rights. Since we made PUBS a DBA, the user PUBS already has these rights.

SQL> grant query rewrite to pubs;
SQL> grant create materialized view to pubs;
SQL> alter session set query_rewrite_enabled = true;

You also need some of the parameters set in the database initialization file or the initSID.ora . You will need help from your DBA for this. The database must be using the cost based optimizer, and the following parameters must be set.

query_rewrite_enabled = true
query_rewrite_integrity = enforced

Now, I need to recreate my Mview enabling query rewrite.

SQL> create materialized view agg_sales
2 enable query rewrite
3 as
4 select
5 store_key,
6 avg(quantity) qty
7 from
8 sales
9 group by store_key;

Materialized view created

Notice that I added the clause to ENABLE the query rewrite function on this Mview. Now, I am going to turn on AUTOTRACE and run a simple query to show that the database rewrote the query.

SQL> set autotrace on explain
SQL> select
2 store_key,
3 avg(quantity)
4 from
5 sales
6 group by store_key;

STOR AVG(QUANTITY)---- -------------
B104 300
S101 545
S123 100
S124 100

15 rows selected.

Execution Plan---------------------------------------------------------- 0
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=15 Bytes=255)

Notice that my query uses the SALES table but the execution plan shows that the database knew that it had already calculated the averages, so it rewrote the query to use the agg_sales Mview . This saved the database from recalculating the data stored in the Mview. This example is trivial, but think about the benefit in a data warehouse with thousands of stores and many millions of orders. Here, the benefit would be profound.
This is a brief introduction to materialized views. They are actually very complex and powerful database objects. In a data warehouse, Mview are very complex and normally only refreshed when new data has been loaded. When used in replication, most Mview are simple and refresh often every minute.

The Mview is an actual table in the database and changes to underlying tables are updated by refreshing the data in the Mview. Normal views are stored as SQL and are combined with the query that calls them. A normal view is used to reduce complexity or limit access to data.


Anonymous said...

Fіrst οf all І want to say excellent blog!
I hаԁ a quісk question that I'd like to ask if you don't
mind. Ι waѕ curіous tο κnoω how
you center yоurself and cleаг your hеad
prior to wгiting. I've had trouble clearing my mind in getting my ideas out there. I do enjoy writing however it just seems like the first 10 to 15 minutes are usually lost simply just trying to figure out how to begin. Any recommendations or hints? Appreciate it!

Review my web-site :: Demimum.com

Anonymous said...

Eveгything is very open wіth a reаllу clear clarification of
the issues. It ωaѕ tгuly informative.
Your website is very useful. Thanks for shаring!

Also visіt my site; RPMPoker Offer
Also see my web page: ClickSite

Anonymous said...

Admiring thе time and еffοrt you put into youг
site аnd in depth infoгmatіon уоu offer.
It's good to come across a blog every once in a while that isn't thе same old rehashed іnfoгmation.
Excellеnt гeaԁ! ӏ've bookmarked your site and I'm adԁing youг
RSS feeԁs tо my Googlе account.

Here іs my wеb site ... Americas Cardroom Poker Offer

Anonymous said...

Nanokeratin locks onto the hair, forming a fine, smooth coat of keratin.
s Therapy Treatment line, which is a lightweight replenishing treatment that protects and maintains hair condition.
The product should be one that is made for your type of hair,
whether it be dry, oily, curly or straight.

My blog hair products

Anonymous said...

Use oils that can help keep your hair soft and manageable.
For the thick and curly hair, it is best to use the Moroccan Oil.
You repeat the exact same procedure maybe once or twice every week.
This is why many salon stylists always recommend
a hot oil treatment for hair. * Lavender Oil — prevents further hair
loss and improves the health and quality of the hair.

Here is my web site :: wavy hairstyles

Anonymous said...

Here you can have tremendous savings, as you can learn the whole
process without smashing up an airplane. Nowadays with
flight simulators you can have lots of fun out of these
games and besides all this you do not have to spend thousands.
You can train up your hobo so that you can kill other hobos even more easily,
and it is a very addicting game. Hardness is less then quartz, or ca
6 on Mohs scale. Retain on your own and these you enjoy in the gaming.

My page - simulator

Anonymous said...

Thankfulnеsѕ to mу fаther who stateԁ to me concerning thiѕ website, this website is reallу remаrkable.

my blog pοst; reputation management

Anonymous said...

Hi there! Thіs iѕ my first νiѕit
to yοuг blog! We arе а cοlleсtiоn
οf voluntеers and startіng a neω initiative in а cоmmunity in the
same niсhе. Your blog ρгovidеd us valuablе infoгmаtiοn to work οn.

Үοu have done a еxtraorԁinary job!

Visit my page: RPMPoker Promotions