Excellent point, Ken./me moves to replace all common vernacular regarding materialized views to be "materialized query result". :)
-jay Ken Jacobs wrote:
I've always thought the term "materialized view" was a little misleading. A materialized view is not used like a view at all in most cases. It may appear like a table (and therefore like a view), but in actuality, most often it is not directly used at all. Users write a query against a (set of) table(s), and the optimizer re-directs the query to the stored result (the materialized view). The user doesn't typically see the materialized view or use it directly. (And the definition below omits this important aspect of the definition of a materialized view.)The DBA also treats a materialized view differently from a normal view, since you can't create indexes on or partition normal views. (Obviously it is because they are materialized in this case that the DBA can do so, but it's not the "viewness" of the object that would be indexed for example, but the stored query result.)So it is the word "view" that I find misleading in this context. It is more like an index in the sense that it is used to transparently improve query performance. It might have been better called a "materialized query result" or "stored result" or even a "query index".But materialized view is what it's called. No going back now! Ken Jay Pipes wrote:FWIW, I think Justin Swanhart did an excellent job outlining the different terms and syntax. I reproduce that here:"Materialization = Materialization is storing either in memory, or on disk, one or more tuples, either permanently, or temporarily.View = A database object which references other database objects, as a SQL SELECT statement. When the view is accessed, ideally, the SQL statement accessing the view will be modified. This is known as "merging" the statement with the view. MySQL isn't able to use the merge optimization very often, and instead materializes the table. This results in low performance when using views.Temporary tables = Temporary tables are a form of materialization which may be done either EXPLICITLY, ie, a temporary table is created and populated by DML, or IMPLICITLY, i.e., when a view is accessed which must be materialized to answer the query which uses the view. Implicit temporary tables are usually not indexed (may not be true in MySQL 6(blah whatever, I'm still calling it MySQL 6)...Materialized View = A special class of database object which appears to be a normal table, but in fact is an object built from data in other tables. Materialized views are first class database objects. Materialized views are normally built from a SQL select statement, and the materialization is updated, either asynchronously or synchronously with table changes.Materialized views can be indexed, partitioned or otherwise structured for improved query performance over "regular' views, particularly regular views which must be temporarily materialized. Implicit materialization is bad for performance and should be avoided wherever possible."Cheers, Jay Roland Bouman wrote:Hi Brian! thanks - this is much clearer! I spotted one typo now:"So "materializing" is out, but what is not out is "materialized tables"."I think you mean "materialized views" i.o. "materialized tables" kind regards, Roland On Thu, Jul 16, 2009 at 12:37 AM, Brian Aker<[email protected]> wrote:Hi! Thanks for the feedback, I've updated the blog to try to clarify my language. Cheers, -Brian On Jul 15, 2009, at 3:31 PM, Roland Bouman wrote:Hi! On Wed, Jul 15, 2009 at 10:38 PM, Brian Aker<[email protected]> wrote:Here is a blog entry wrote up on the state of Views/Triggers: http://krow.livejournal.com/641510.html Comments either here or there are welcome!No offense but to me your wording is at times quite confusing. Look at this: " There are plans to add "non-materializing views", but that is still a couple of milestones off. ... One of the problems when talking about views is that the word 'materialize' has been over used. To 'materialize' a view, it means that you take the view definition, turn it into a temporary table, and then join it against a query. In Drizzle we consider this a 'no no'. ... Subqueries at this point are only semi-useful. Many of the common methods of using them result in a 'derived' table, aka a materialized table, which just makes them too slow. ...So 'materializing' is out, but what is not out is 'materialized joins'.This is an SQL feature whereby a table is created and is updated based on entries from other tables. This is something I do expect us to support at some point. .... We can also have materialized views via the same method. The 'tires' on this project have been kicked around but no one has started it yet. ... Materialized Views are pretty cool, materializing a view though pretty much make you want to cry. " All documentation I have seen so far uses the term "materialized view" to denote a special class of database view objects that use a base table to cache the result of the SQL query underlying the view. This is also what Justin is referring too AFAICS. For that reason, the term "non-materializing view" you used in the second paragraph is confusing to me - at a glance it looks like you mean "views, not materialized views" but I think you meant in fact views that can be evaluated without first evaluating the views' underlying query and storing the result in a temporary table. Later on you refer to the term "materialized join". I have never seen that term, except as part of "materialized join view", in which case it refers to a materialized view that has an underlying query that contains a join. In your post, you seem to use the term "materialized join" to denote what is usually referred to as "materialized view" - ("This is an SQL feature whereby a table is created and is updated based on entries from other tables. "). A materialized view need not be based on a join query at all (I mean, a query involving a JOIN operation), therefore it is confusing to use the term "materialized join" if you actually mean "materialized view". Another point that I found confusing is your use of the term "derived table". You seem to use that term to denote any case where a subquery is first evaluated and stored in a temptable for later use. So far, I've seen the term "derived table" being used exclusively for a special class of subqueries, "a subquery in the FROM clause", also known as an "inline view". The term derived table is normally used to denote this syntax construct, regardless of whether the unerlying implementation has to first evealuate the subquery and store it in a temporary table. So I guess I'm saying I'm not really sure what you mean anymore when you say "materialized view". kind regards, RolandCheers, -Brian _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp-- Roland Bouman http://rpbouman.blogspot.com/ Author of "Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL", http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
