Author Archives

Coincindences

I had another of those odd timing events today that make me think that Larry Ellison has access to a time machine. I found (yet another example of a) bug that had been reported on MoS just a few days before it appeared on an instance I was running. How is it possible that someone keeps doing […]

Bitmap loading

Everyone “knows” that bitmap indexes are a disaster (compared to B-tree indexes) when it comes to DML. But at an event I spoke at recently someone made the point that they had observed that their data loading operations were faster when the table being loaded had bitmap indexes on it than when it had the equivalent B-tree indexes in place. There’s a good […]

NVL() change

One of the problems of functions is that the optimizer generally doesn’t have any idea on how a predicate based on function(col) might affect the cardinality. However,  the optimizer group are constantly refining the algorithms to cover an increasing number of special cases more accurately. This is a good thing, of course – but it does mean that […]

NVL() change

One of the problems of functions is that the optimizer generally doesn’t have any idea on how a predicate based on function(col) might affect the cardinality. However,  the optimizer group are constantly refining the algorithms to cover an increasing number of special cases more accurately. This is a good thing, of course – but it does mean that […]

Cache anomaly

Just a quick heads-up for anyone who likes to play around with the Keep and Recycle caches. In 11g Oracle introduced the option for serial direct path reads for tablescans on tables that was sufficiently large – which meant more than the small_table_threshold – provided the table wasn’t already sufficient well cached.  (The rules mean […]

Cache anomaly

Just a quick heads-up for anyone who likes to play around with the Keep and Recycle caches. In 11g Oracle introduced the option for serial direct path reads for tablescans on tables that was sufficiently large – which meant more than the small_table_threshold – provided the table wasn’t already sufficient well cached.  (The rules mean […]

Easy – Oops.

A question came up on OTN today asking for suggestions on how to enforce uniqueness on a pair of columns only when the second column was not null. There’s an easy and obvious solution – but I decided to clone the OP’s example and check that I’d typed my definition up before posting it; and the […]

Easy – Oops.

A question came up on OTN today asking for suggestions on how to enforce uniqueness on a pair of columns only when the second column was not null. There’s an easy and obvious solution – but I decided to clone the OP’s example and check that I’d typed my definition up before posting it; and the […]

Tweaking

The following question came up on OTN recently: Which one gives better performance? Could please explain. 1) nvl( my_column, ‘N’) <> ‘Y’ 2) nvl( my_column, ‘N’) = ‘N’ It’s a question that can lead to a good 20 minute discussion – if you were in some sort of development environment and had a fairly free hand […]

Tweaking

The following question came up on OTN recently: Which one gives better performance? Could please explain. 1) nvl( my_column, ‘N’) <> ‘Y’ 2) nvl( my_column, ‘N’) = ‘N’ It’s a question that can lead to a good 20 minute discussion – if you were in some sort of development environment and had a fairly free hand […]