Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View question
icon5.gif  Materialized View question [message #240394] Thu, 24 May 2007 13:47 Go to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
I just googled the following about Materialized Views ...

"When they are used within a database, they are used much like an index; for performance. In addition, like an index, they should be able to be dropped without affecting an existing query. In other words, your code should not be dependant upon their existence."

Does this mean that I should not create a regular view or procedure that explicitly references a materialized view that I created?

Thanks.

[Updated on: Thu, 24 May 2007 14:30]

Report message to a moderator

Re: Materialized View question [message #240402 is a reply to message #240394] Thu, 24 May 2007 15:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This means that you create your query without taking into account if there is a MVIEW or not.
But if your MVIEW is explicitly created like a regular view for other reasons than performances, then you can directly use in the query.
The sentence:
Quote:
they should be able to be dropped without affecting an existing query.

is misleading. They affect the queries in their performances. MVIEW and indexes are there to improve query performances, dropping them will greatly slow down them.

Regards
Michel
Re: Materialized View question [message #240439 is a reply to message #240402] Thu, 24 May 2007 19:18 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The intent of the original quote seems directed at MV's that are used for QUERY REWRITE.

There are many legitimate uses for MVs other than query rewrite - namely replication.

But even forgetting replication, MVs can be an efficient technique for building some components of a Data Warehouse ETL - eg. To move data between the 3NF and the star-schema layers. In such cases, you write your queries against the dimensional model (MVs), not against the 3NF model.

Ross Leishman
Re: Materialized View question [message #241109 is a reply to message #240439] Mon, 28 May 2007 06:08 Go to previous message
gintsp
Messages: 118
Registered: February 2007
Senior Member
rleishman wrote on Fri, 25 May 2007 03:18
The intent of the original quote seems directed at MV's that are used for QUERY REWRITE.

Ross Leishman


BTW also query rewrite is available only in enterprise edition however MVs as such are available in other editions as well. So not all people in the world are using only enterprise edition Wink

Gints Plivna
http://www.gplivna.eu
Previous Topic: finding the number of working days between the start day and end day (merged)
Next Topic: analytical sql statements
Goto Forum:
  


Current Time: Fri Dec 09 15:49:46 CST 2016

Total time taken to generate the page: 0.36954 seconds