Home » RDBMS Server » Performance Tuning » about materialized views
about materialized views [message #229338] Fri, 06 April 2007 14:50 Go to next message
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 #229375 is a reply to message #229338] Sat, 07 April 2007 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1.
Did you collect statistics after creating your MV?
Did your session parameter QUERY_REWRITE_ENABLED set to TRUE or FORCE?
Did your optimizer mode set to ALL_ROWS, FIRST_ROWS, or FIRST_ROWS_n?
What is the value of your parameter QUERY_REWRITE_INTEGRITY? Are all the constraints enabled and validated?
Maybe other things...

2.
It depends on your version.
10g deletes so no impact on other transactions,
before (IIRC) there is a truncate with the problem for other queries.

3.
Fast refresh is transactionnal and follows the MV log.
The rules are the same as with other ("normal") transactions, i.e. ACID.

Regards
Michel
Re: about materialized views [message #229378 is a reply to message #229338] Sat, 07 April 2007 01:35 Go to previous messageGo to next message
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

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 messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
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 messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
Michel Cadot
Messages: 64103
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 messageGo to next message
rleishman
Messages: 3724
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
Michel Cadot
Messages: 64103
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
Previous Topic: fastest way for delete cascade
Next Topic: decode
Goto Forum:
  


Current Time: Sun Dec 04 21:00:36 CST 2016

Total time taken to generate the page: 0.08900 seconds