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?


13 thoughts on “Marketing of Materialized Views

  1. 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.

  2. 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.

  3. 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.



  4. 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.

  5. 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?

  6. 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.”

  7. 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 :)

  8. 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.

  9. David,

    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.


  10. 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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s