Rittman Mead Blog - Oracle Database Technology

Tagged

Oracle Database

A collection of 141 posts

Converting partition views to partitioned tables

A few days I was talking to a customer who has a legacy data warehouse, designed back in Oracle 7.3 days which relies extensively on partition views as a way of handling the volume of data involved (basket level sales from a national high-street chain). Over the years the

Bye-bye Oracle 9.2

Well the detailed planning for a customer's data warehouse upgrade paid off. We kept mainly to plan until the customer tried using their query tool. We then spent what seemed an age, late at night, tracking down a problem which almost had the customer deciding to revert to

Table compression

As I mentioned yesterday I was in London (in fact in the shadows of Westminster Abbey and the Houses of Parliament) today for the UK OUG BIRT SIG meeting. The venue was also shared by a UK OUG directors meeting so I got the opportunity to chat to Peter Robson

More on the 9.2 to 10.2 upgrade

But first. It's good to see that two bloggers in the Washington DC area are back on-line. Welcome back Tom [http://tkyte.blogspot.com/] and Sponge [http://oraclesponge.wordpress.com/] A few posts ago I mentioned [https://www.rittmanmead.com/blog/2007/08/31/upgrading-to-10g-ok-im-a-bit-behind/] my final 9

Upgrading to 10g - OK, I'm a bit behind

At long last, my final data warehouse customer on Oracle 9 is moving on to 10g. It has been a somewhat protracted move as for reasons of vendor support certification we had to adopt a specific order of refreshing their whole technology stack. That's not to say we

Reading time - Oracle 11g

There you are - I've mentioned it. But I have not downloaded it yet. One of the things I do with any new release is to read some (no, not all) of the documentation. The new features documentation, of course, is a given and enough of the of

Moving partition statistics

Over in another place [http://oracledoug.com/serendipity/index.php?/archives/1306-Full-Volume-Database-Refresh-Part-3.html] , Doug Burns has been writing on the joys of reproducing a full size database as test instance. He has even (graciously) put up with my wittering about things that not relevant to his articles. So as not

Oracle Partitioning 2007

I see that Oracle have posted [http://www.oracle.com/technology/products/database/oracle11g/index.html] a series of white papers on the soon to arrive 11g release, the one on partitioning [http://www.oracle.com/technology/products/bi/db/11g/pdf/partitioning-11g-whitepaper.pdf] caught my eye. But first an

Playing Around with Star Transformations and Bitmap Indexes

I’ve been doing some work this week with a client in London, who wants to put together a reporting data mart based on Oracle Database 10gR1. Although they and I were fairly sure a dimensional database design using bitmap indexes and star transformations would give them the query performance

Look Before You Leap

A recent post on Howard Rogers' blog around the hype and enthusiasm around the launch of Oracle 11g [http://www.dizwell.com/prod/node/881], together with some feedback I got during the review process for my OU seminar slides, got me thinking about how we sometimes rush in

A Day of Product Releases

Today was the official launch of Oracle Database 11g in New York City, whilst another event, the Oracle Fusion IT Conference, was being held at the Emirates Stadium in London. I couldn't make it to either as I was onsite with a customer, but the New York event

I know that tune

Certain solitary acts give me great pleasure. Oddly, I consider the intellectual challenge of getting some of my customers' queries to fly a solitary act, it's me against the database. Or perhaps me against the customer. I have been working on a set of poorly performing queries

Group By Rollup and other exotica

One of the topics I have wanted to research, but have never managed to find time to, is a performance comparison of Oracle OLAP technology and the SQL grouping extensions such as GROUP BY ROLLUP, GROUP BY CUBE and GROUP BY GROUPING SETS. I suppose for that mater I should

Second Day of the Miracle DB Forum, Edinburgh

Today was Day Two of the Miracle DBF up at Edinburgh Castle, and for those of us that got a reasonably early night it was a Nine O'Clock start with Mogens Norgaard on "You Probably Need RAC". Following on from Mogens was Dan Fink with "

Posting from the Miracle DBF, Edinburgh

I'm currently sitting in a pub just down from Edinburgh Castle, in a break between the morning and afternoon sessions. Just across the table from me is Doug Burns, and behind is Thomas Presslie and Carel-Jan Engel, who are working on the Miracle musical for tonight's

Query tuning

Yesterday I mentioned a query that would not eliminate unused partitions and resulted in a somewhat large nested loop join plan that would take hours to return. A kind of "find all the rows that match predicate B and then filter the result set on a range of 7

Constraints

Constraints do just that; when enforced they constrain data such that rules are obeyed. But even when not enforced the existence of an Oracle RELY constraint does something really useful, it tells the CBO, and especially the query rewrite engine, that certain facts about the data can be presumed to

Lag, lead and sparse data

Jonathan Lewis posts a nice example [http://jonathanlewis.wordpress.com/2007/04/09/log-switching/] (with a subsequent comment from Jeremy [http://www.ardentperf.com/] expanding it to work well with RAC) of an use of the analytic LEAD function. Which reminds me of a gotcha with LAG or LEAD with

Pattern matching

Tom Kyte posts [http://tkyte.blogspot.com/2007/04/so-in-your-opinion.html] an interesting paper and invites comment. It is not an easy read, and really needs a reasonable understanding of analytic functions, but persevere if you can. To my mind the best place to find patterns is as close to

End of week catch up

A somewhat slow week blogwise; loads of events that are not blog worthy, or just unusable for confidentiality reasons. Some may be written about later, but others will be locked away for ever. At long last one of my customers is going to upgrade the Oracle version of there multi-terabyte

Materialized Views - 2

The other day I was asked [https://www.rittmanmead.com/blog/2007/02/07/working-with-materialized-view-summaries/#comment-5140] about the approach I took to refreshing materialized views. To be honest I use my own techniques - others [http://oraclesponge.wordpress.com/2006/04/12/a-quick-materialized-view-performance-note/] do similar things. In a data warehouse

Working with materialized view summaries

Materialized views (mviews) have been part of Oracle for along time, before Oracle 8 they went under the name snapshots and indeed some of the current dictionary tables refer to the old name. Common uses of mviews are to replicate data and to summarise data. It's this second

Not such good ideas

One of our consultants dropped by my desk to ask me some Oracle questions around data life cycle management. One of his customers is moving a store based system to a central system and like a lot of systems in the UK needs to keep 7 years of transactional data

Database problems

One of our customers is on Oracle 9.2, we would dearly love to move them to 10g but for certification reasons with their chosen query tool we can't upgrade them until the tool is also upgraded which in turn is linked to the version of JVM associated

Index organized tables in data warehouses

Like a lot of newfangled stuff, IOTs are perhaps less commonly used in DW systems than they might be. In part this is an extension of the "I've always done it like this, so why change" attitude, and in part it identifying a suitable use where