.comment-link {margin-left:.6em;}

Oracle Sponge -- Now Moved To Wordpress

Please use http://oraclesponge.wordpress.com

Wednesday, August 03, 2005

Marketing of Materialized Views

I was having a casual browse of the Oracle website today, like you do, and noticed in a side panel here the following phrase: "The materialized view can be thought of as a special kind of view that physically exists inside the database."

A less reasonable person would have instantly vomited at such a tortured description. Fortunately I'm made of sterner stuff. Here are some descriptions that I thought up to do an even worse job of describing the feature.

"The materialized view can be thought of as a special kind of view that holds material."
"The materialized view can be thought of as a special kind of table that is also a view."
"The materialized view can be thought of as a special combination of a view and its data."

I don't know, are they worse or not?

In attempting to be positive I tried to think of a better one sentence description.

"A materialized view is a SELECT query definition stored in the data dictionary for which the complete result set is also stored, in a table logically associated with the query."

What do you reckon? Any improvements you can suggest?


At 10:38 PM, Anonymous Eddie Awad said...

There is no shortage of "materialized view" definitions. A quick Oracle documentation search produces the following three definitions (I stopped at three, maybe there is more):

1: A materialized view provides indirect access to table data by storing the results of a query in a separate schema object.

2: Materialized views are query results that have been stored in advance so long-running calculations are not necessary when you actually execute your SQL statements.

3: A pre-computed table comprising aggregated or joined data from fact and possibly dimension tables. Also known as a summary or aggregate table.

At 11:56 PM, Blogger David Aldridge said...

Probably because we're looking at single sentences here, none of those really hit the mark for me. Not that it's a very importabnt issue though, it's really an academic exercise.

Anyway, the reason that none of them seem satisfactory is that the storage of the query defining the result set seems to be too vital a feature of an MV to go unmentioned. In other words, all you need to store the results of a query is a table on its own and some mechanism for reading that data instead of the data from the original table, and this is done all the time through various query tools such as Microstrategy etc.

At 3:38 AM, Blogger Tim... said...

Looks like others are also struggling to find a nice definition that is not bloated, but conveys the message:


What about:

1: It's a view and a table mixed together with a bit of pixie dust!

2: It's a renamed snapshot with a bit of pixie dust!

Perhaps there isn't a sentence for this. Maybe it's just one of those things that requires a paragraph.



At 1:09 PM, Blogger Robert Vollman said...

How about:
"A materialized view is none of your business. Stick to SELECT statements and such, and leave the advanced crap to us."

I know, I know ... that's 2 sentences.

At 3:05 AM, Anonymous Zulq Alam said...

Perhaps, a materialized view is a view for which the results are cached in a table of the same name.

I know its not a cache in the normal sense but it does in fact serve the purpose of a cache, no?

At 7:46 AM, Blogger Joel Garry said...

How 'bout "A materialized view is an oxymoron that describes the violation of Codd's rules of physical and logical independence, so that data may be accessed faster in exchange for data stagnation issues."

At 9:03 AM, Blogger Tim... said...

It doesn't quite have the "buy me!" ring to it that Oracle would like, but I guess it is one sentence so it works for me :)

At 11:53 AM, Anonymous John Spencer said...

Given numerous postings on various sites using Materialized Views for enforcing data integrity rules and other stuff, how about:

A Materialized View is a state of mind, with a query and maybe some data associated.

At 3:00 PM, Anonymous Anonymous said...


My take :

A materialized view is a named result set generated from a select query. The result set can be queried, but remains unchanged until an explicit refresh.


At 4:19 PM, Blogger David Aldridge said...

Two sentences!

At 6:52 PM, Blogger Rahul said...

1: It's a view and a table mixed together with a bit of pixie dust!

Thats the way I have always thought of materialized views.

At 7:44 PM, Blogger David Aldridge said...

Whatever it is, there's pixie dust in there for sure.


Post a Comment

<< Home