Skip to content





Author Archives David Aldridge

Has Everyone Forgotten “Keep It Simple, Stupid”?

OK, we all love a clever piece of code to do something a little bit tricky, but has everyone lost sight of the benefits of simplicity?
My case study for the day, a thread on the Oracle forums in which various all_objects, user_objects, connect by, MODEL, xmltable, and pipelined function techniques are suggested for generating a [...]

Posted 02 July 2008 § Uncategorized ‡ Comments Off °

The “OVERLAPS” Predicate

I was browsing a pretty interesting “e-book” this evening: “Developing Time-Oriented Database Applications in SQL” By Richard T. Snodgrass. I quietly bemoaned the missing functionality of Oracle in some regards, for instance the rather neat OVERLAPS predicate that returns true when two intervals … well, overlap. We tend to jump through hoops a little to [...]

Posted 13 June 2008 § Uncategorized ‡ Comments Off °

A Fix for Check Constraints That Harm Cardinalities

I wrote a while ago about the harmful effect of check constraints on query optimization. I’ll pause while you catch up on that …
Anyway, this appears to have been addressed in 10.2.0.4, and it’s now safe to go back into the water on applying check constraints. Bug 5891471.
The bug description also notes that you can [...]

Posted 21 May 2008 § Uncategorized ‡ Comments Off °

Testing a No-statistics Environment: Part II

Following on from the previous post, a little glitch in the plans: the instance appeared to crash late last night during the load. Hopefully not some exotic bug associated with dynamic sampling.
I modified the delete-and-lock strategy yesterday after remembering that we have a number of indexes that are created with the “compute statistics” option. Since DBMS_STATS.LOCK_TABLE_STATS is [...]

Posted 07 May 2008 § Uncategorized ‡ Comments Off °

Testing a No-statistics Environment.

I am absolutely sick and tired of dealing with issues where misleading partition statistics (for example) cause ETL queries to go crazy and try to sort-merge tables with tens of millions of rows each.
Since we’re about to run a test load on production data I’m going to drop all table and index statistics and lock [...]

Posted 06 May 2008 § Uncategorized ‡ Comments Off °

An Empassioned and Meaningless Debate

“See-qwel” or “S-Q-L”? Have your say on this highly important matter here, and help shape the future of our industry.

Posted 25 April 2008 § Uncategorized ‡ Comments Off °

ETL Powered by Rice and Beans

I’m told that it’s very important to keep one’s brain in shape as one grows older. Or “drifts gently towards the twilight” as I prefer to think of it. Trying to look on the optimistic side, obviously. Fortunately I end each day with my brain wrung out like a well-used sponge courtesy of some ETL development work [...]

Posted 17 April 2008 § Uncategorized ‡ Comments Off °

Choosing An Optimal Stats Gathering Strategy

Greg Rahn has an excellent entry on statistics gathering and the use and abuse of dbms_stats and initialization parameters at the Structured Data blog, one of my favourites. I added a comment on dynamic sampling, of which I am a big proponent, and Greg has another recent post on a case where dynamic sampling saved [...]

Posted 13 April 2008 § Uncategorized ‡ Comments Off °

Indexing Options for Change Data Capture

I just posted this question to the Oracle list, but thought I might post it here in case that reaches a wider audience. It also may be a situation that others in the DW arena have faced before.
 

I have a large and busy OLTP table, 100GB or so, against which there is a need to capture [...]

Posted 09 April 2008 § Uncategorized ‡ Comments Off °

Stored Procedure are Wonderful/Evil

The same old “stored procedures are wonderful/evil” debate is again being practiced at the Joel on Software forums.
Enjoy.

Posted 07 April 2008 § Uncategorized ‡ Comments Off °