Presentation of Summaries and Materialized Views

I submitted an abstract for a presentation at the Rocky Mountain OUG Training Days 2007, on the subject of materialized views, summaries, fast refresh and whatnot. Obviously a lot of the substance will come from the material I’ve been posting here for the past year-and-a-bit, emphasising conceptual understanding, analysing the internals of the refresh process, a caveat here and there, the ongoing search for optimisation techniques, and with other material to tie it together as a coherent topic.
Since this is aimed at all levels of experience, from “what is a materialized view” through to “Ah, but in the case of partitioning of a HOLAP-based MV …” what issues would you like to see covered in the talk?

Here is what I have so far, in no particular order …

  • When and what to summarise.
  • Components of a materialized view (table, metadata etc).
  • Query rewrite.
  • Complete and fast refresh.
  • Fast refresh following conventional path, direct path, and partition DDL operations.
  • Propagating changes to the MV data.
  • Performance issues.
  • Alternatives to MV use and tuning manual refresh of summaries.
  • DBMS_Advanced_Rewrite
  • Caveats (32-column limitation on GROUP BY, ORA-12034, PCT and prebuilt tables etc)

A lot of ground to cover, but I’ve optimistically applied for a 90 minute slot — I think I could manage double that at a pinch!

What would you emphasise out of that, and what would you add?


10 thoughts on “Presentation of Summaries and Materialized Views

  1. I’d add a section to deal with changes. As in:

    “development” wants to change a column or add columns or something else in a MV:
    what do you do, options, alternatives, size issues, refresh issues, re-indexing, online/offline issues, etc.

    The practical aspects that are sadly missing from most MV doco I’ve seen.

    And yes: it happens…

  2. Thanks Don,

    I think I’ll have that covered, although that’s certainly a snappier title for what I have in mind. I have a little bit of algebra to illustrate the case for summary data in terms of net resource savings, and in general I doubt that there’re many data warehouses that wouldn’t benefit from at least some summarisation. With that in mind the issue reduces to “Which summaries are right for you” and whether to implement tham as MV’s or as summaries with manual maintenance and DBMS_Advanced_Rewrite.

  3. Popular misconceptions? Give the audience a few jokes.

    Query rewrite is a good topic to expand on – what rewrites and what doesn’t and the importance of constraints and dimension objects to get it to work – you could even capture wacky BO queries and prove it will rewrite!

  4. Heh, I’m not sure what misconceptions would count as popular. Maybe that would come out in the questioning. I’ll have to think back to some of my co-workers misconceptions and assume that they’re popular :)

    Yes, query rewrite is an interesting topics. The “trouble” with it is that it just works so well, IMHO. As long as you follow the principles of declaring all dimensions and constraints that you can, and as long as you don’t throw anything too funky at it (analytic functions etc) then it just … works. I wonder if that’s why there is so little written about it? Even stuff like “join back”, which is a topic heading in itself in the documentation, seems to be rather an obvious feature — I remember being slightly startled that it was documented as it seemed rather obvious. The other distressing feature of queryt rewrite is that it is very well documented :( and I’m inclined to say, “Here’s a list of other query rewrite features — go and read about them”.

    I’ll certainly cover the diagnostic stuff in DBMS_MView.Explain_Rewrite, which I have used successfully quite a bit.

  5. Hi David,

    I am starting on the path of a data warehouse designer and currently my focus is on deciding an aggregate strategy. I am finding your blog (along with Peter Scott’s) an excellent resource of expert advice.

    I am quite interested in this presentation of yours, because the subject matters seems immensely relevant to the task I have at hand. So if it’s not going to violate any copyrights and if you don’t mind sharing it, could you please send the ppt/pdf to me via email.

    Many thanks.

  6. Hi David,

    Just following up on my earlier note – I checked the RMOUG 2007 presentations, and found one presentation from you on ‘Linux I/O schedulers’. So is it that the MV presentation mentioned in this blog entry never got written/published?


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