Quite some time ago I whined about the issue of getting excessive head movement on long sequential reads of the type that data warehouses commonly use. The problem, to paraphrase my earlier article, was that during a read of a large amount of contiguous data the operating system/RDBMS would be too amenable to satisfying other read requests from the same disk, hence incurring head movement too frequently. Continue reading
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.
- 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?
Should you ever find yourself in Lincoln, Nebraska, don’t make things worse by eating here.
Someone sent me a scenario the other day similar to this:
A small dimension table, for example of US State names, has a synthetic primary key (say STATE#) and a unique key on the real-world value (STATE_NAME), and a much larger fact-type table has an indexed foreign key to this lookup table. The distribution of values in the fact table is very skewed. Is it possible for the optimizer to take into account the skew of STATE# in the fact table when it is joined to the dimension table and a predicate is placed on STATE_NAME?
Well, a tricky issue. Continue reading
… and in rare form too, with a new blog entry on a prolasped rectum incident. Not his own, I should add, a horse’s. I particularly liked, “…I’ve seen things so gross that they would gag a maggot”.
My advice, make sure he washes his hand before you shake it!
… the latest Oracle ACE.
Howard‘s work has saved me much aggravation over the past months, most notably in terms of Oracle on Linux installs and VMWare, but he also fills many gaps in my regular DBA knowledge on backup and recovery for example, which I don’t do nearly often enough to be competent at.
Well-deserved recognition, and according to my simplistic page searching methods he becomes the only Oracle ACE resident in the sunburnt country, which ought to be worth something. He’s even an actual Aussie now, so it’s all bona fide.