about materialized views [message #229338] |
Fri, 06 April 2007 14:50 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
laiko
Messages: 10 Registered: February 2007
|
Junior Member |
|
|
I have a few questions on materialized views:
1. I have a query that exactly matches a materialized view. I defined the Materialized view with enable query rewrite. However, when I run the query, it is not hitting the materialized view. What can be the reason?
2. When I do a complete refresh, does it truncate the table - hence, making the data unavailable? How about if I am accessing the mv and suddenly someone else refreshes it?
3. In case of fast refresh, does it do a delete/insert or update? I believe that data will be available during the refresh - but how about if the row I am querying is the one that is in the materialized view log?
Thanks for any input you may give..
|
|
|
|
Re: about materialized views [message #229378 is a reply to message #229338] |
Sat, 07 April 2007 01:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
gintsp
Messages: 118 Registered: February 2007
|
Senior Member |
|
|
laiko wrote on Fri, 06 April 2007 22:50 | I have a few questions on materialized views:
1. I have a query that exactly matches a materialized view. I defined the Materialized view with enable query rewrite. However, when I run the query, it is not hitting the materialized view. What can be the reason?
|
statistics, query_rewrite_integrity, global_query_rewrite, qualifying table names with schema name?
for more info you can read either Oracle docs datwarehousing guide chapters basic and advanced query rewrite or check my article Effective search in a normalized application chapters
"Privileges and settings necessary to use query rewrite for the same user objects" and "Privileges necessary for other users to use query rewrite" at http://www.gplivna.eu/papers/mat_views_search.htm
Quote: |
2. When I do a complete refresh, does it truncate the table - hence, making the data unavailable? How about if I am accessing the mv and suddenly someone else refreshes it?
|
depends on version
in 9i the complete refresh is truncate, insert /*+ append */
in 10g the default is delete, conventional insert, but using dbms_mview.refresh parameter atomic_refresh = false you can revert back to 9i mechanism
If you always need to use query rewrite in 9i you can use for example mechanism described in above mentioned article
Quote: |
3. In case of fast refresh, does it do a delete/insert or update? I believe that data will be available during the refresh - but how about if the row I am querying is the one that is in the materialized view log?
|
You can run 10046 level trace and see how oracle internally does it. So you'll also get better understanding why fast refresh sometimes might be quite not so "fast" ![Smile](images/smiley_icons/icon_smile.gif)
Gints Plivna
http://www.gplivna.eu
|
|
|
Re: about materialized views [message #229380 is a reply to message #229375] |
Sat, 07 April 2007 01:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
You can use DBMS_MVIEW.EXPLAIN_MVIEW to find out why it is not performing query rewrite.
Materialized views are not ideal for guaranteeing availability during refresh. I don't think Michel's blanket statement that 10g performs deletes is completely accurate. I have implemented MVs on 10g that definitely truncate for both Complete and PCT refresh.
Fast refresh performs delete/insert (I've NEVER seen it do an update). If you have PCT enabled on the MV, it may perform a partition truncate - but only if you DROP/EXHANGE/TRUNCATE a partition in the master table.
The important thing to note is that nowhere in the manual (at least nowhere I could find) guarantees that data will remain available during refresh. You could experiment with the ATOMIC option of DBMS_MVIEW.REFRESH - the idea of this option is that when you refresh MANY MVs, they either all succeed or they all fail. Logically, Oracle would not be able to truncate in this situation unless you passed it a single MV.
You could experiment on a single MV both with and without the ATOMIC option and see what happens. The important thing to remember is that unless you find something in the manual the GUARANTEES data availability during refresh, a future upgrade may implement somthing you don't want.
Ross Leishman
|
|
|
Re: about materialized views [message #229409 is a reply to message #229380] |
Sat, 07 April 2007 07:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
gintsp
Messages: 118 Registered: February 2007
|
Senior Member |
|
|
rleishman wrote on Sat, 07 April 2007 09:39 |
I don't think Michel's blanket statement that 10g performs deletes is completely accurate. I have implemented MVs on 10g that definitely truncate for both Complete and PCT refresh.
|
SQL> conn xxx/xxx@xxx
Connected.
SQL> select version from v$instance;
VERSION
-----------------
10.2.0.1.0
SQL> create table test (a number not null);
Table created.
SQL> insert into test select rownum from dba_users;
34 rows created.
SQL> commit;
Commit complete.
SQL> create materialized view test_cnt
2 refresh complete on demand
3 enable query rewrite
4 as select count(*) from test;
Materialized view created.
SQL> exec dbms_stats.gather_table_stats(user, 'test')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user, 'test_cnt')
PL/SQL procedure successfully completed.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> exec dbms_mview.refresh('test_cnt', atomic_refresh=>true)
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh('test_cnt', atomic_refresh=>false)
PL/SQL procedure successfully completed.
So the first time I used atomic_refresh=>true, second time atomic_refresh=>false.
Trace file contained following rows (of course quite many rows skipped). As you can see first time delete is used and second time truncate.
PARSING IN CURSOR #28 len=66 dep=0 uid=60 oct=47 lid=60 tim=2665224989 hv=1147574116 ad='251c39cc'
BEGIN dbms_mview.refresh('test_cnt', atomic_refresh=>true); END;
END OF STMT
PARSING IN CURSOR #40 len=53 dep=1 uid=60 oct=7 lid=60 tim=2666008092 hv=2672436950 ad='219370d4'
/* MV_REFRESH (DEL) */ delete from "GINTS"."TEST_CNT"
END OF STMT
PARSING IN CURSOR #40 len=129 dep=1 uid=60 oct=2 lid=60 tim=2666025488 hv=4050016697 ad='2a4b42c0'
/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "GINTS"."TEST_CNT"("COUNT(*)") SELECT COUNT(*) FROM "TEST" "TEST"
END OF STMT
PARSING IN CURSOR #41 len=67 dep=0 uid=60 oct=47 lid=60 tim=2674847160 hv=1029517213 ad='25117ea4'
BEGIN dbms_mview.refresh('test_cnt', atomic_refresh=>false); END;
END OF STMT
PARSING IN CURSOR #50 len=75 dep=1 uid=60 oct=85 lid=60 tim=2675070150 hv=2256023461 ad='25d986a4'
/* MV_REFRESH (DEL) */ truncate table "GINTS"."TEST_CNT" purge snapshot log
END OF STMT
PARSING IN CURSOR #50 len=137 dep=1 uid=60 oct=2 lid=60 tim=2675482059 hv=131783386 ad='218a6704'
/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND */ INTO "GINTS"."TEST_CNT"("COUNT(*)") SELECT COUNT(*) FROM "TEST" "TEST"
END OF STMT
Gints Plivna
http://www.gplivna.eu
|
|
|
Re: about materialized views [message #229460 is a reply to message #229409] |
Sun, 08 April 2007 07:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
So it looks as though atomic_refresh is currently forcing it to delete instead of truncate. Repeat my advice though: beware any reliance on this unless you find it documented in the manual.
Also: thanks Gints - I had not noticed the PURGE SNAPSHOT LOG extention to TRUNCATE before - very handy - TRUNCATE can take ages when it has to update thousands or even millions of rows in the log. Previously I have had to remember to truncate the log first.
Ross Leishman
|
|
|
Re: about materialized views [message #229469 is a reply to message #229460] |
Sun, 08 April 2007 13:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
It is documented.
Implicitly, in every procedure using atomic_refresh parameter when it is talked about single vs multiple transactions.
A little more explicitly, in "Database Data Warehousing Guide", Chapter 15 "Maintaining the Data Warehouse":
Quote: | For refresh using DBMS_MVIEW.REFRESH, set the parameter atomic_refresh to FALSE.
- For COMPLETE refresh, this will TRUNCATE to delete existing rows in the materialized view, which is faster than a delete.
- For PCT refresh, if the materialized view is partitioned appropriately, this will use TRUNCATE PARTITION to delete rows in the affected partitions of the materialized view, which is faster than a delete.
- For FAST or FORCE refresh, if COMPLETE or PCT refresh is chosen, this will be able to use the TRUNCATE optimizations described earlier.
|
In details, in Metalink note 306502.1: "After Upgrade To 10g, Materialized View Refresh Use Delete Instead Of Truncate".
Regards
Michel
|
|
|
Re: about materialized views [message #229746 is a reply to message #229469] |
Mon, 09 April 2007 22:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Yeah, this is pretty compelling. But I'd still like to see something that guarantees the MVs are not left in an unusable state if the refresh fails.
Ross Leishman
|
|
|
Re: about materialized views [message #229761 is a reply to message #229746] |
Mon, 09 April 2007 23:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
If it is atomic_refresh, the answer is yes as it is guarantee by the transaction processing. If you don't trust it then you don't trust any work made by Oracle.
If it is not atomic_refresh, then your MV may be empty if refresh fails.
Regards
Michel
|
|
|