Skip to content

Author Archives Jonathan Lewis

Oracle Versions

Having discovered that it’s now easy to create polls, I find that it’s a little addictive. There have been requests for help going all the way back to 7.3 fairly recently on OTN, so I thought I’d set up a poll to see which versions people had in production. If I’ve got it right you’ll [...]

Posted 01 September 2010 § Uncategorized ‡ Comments Off °

Filter “Bug”

Here’s an example to remind you how important it is to look at the “Predicate Information” supplied with an execution plan. Here are two execution plans that look very similar in shape – a continuous set of steps inwards and downwards from parent to child, with no “multi-child” parent rows: ————————————————————————– | Id  | Operation                      [...]

Posted 31 August 2010 § Uncategorized ‡ Comments Off °

Subscribers

Some time ago I added the “subscribe” option to the right-hand panel to allow people to register for automatic email whenever I posted a new article. (I’ve also checked how easy it is to unsubscribe if you change your mind  - and it’s very easy) Since then I haven’t been paying attention to how many [...]

Posted 30 August 2010 § Uncategorized ‡ Comments Off °

Fair Comparison

From time to time someone will post a question about query performance on the OTN database forum asking why one form of a query returns data almost immediately while another form of the query takes minutes to return the data. Obviously there are all sorts of reasons – the optimizer is not perfect, and different [...]

Posted 29 August 2010 § Uncategorized ‡ Comments Off °

Coming Soon

My trip to India for Sangam 10 is getting very close so I thought I’d pop this to the top of the list one last time. And looking ahead to the middle of September, it looks as if I’m going to spend more time talking about Oracle than doing things with Oracle: AIOUG September 3rd/4th: [...]

Posted 28 August 2010 § Uncategorized ‡ Comments Off °

Quiz Night.

I was on a customer site recently where I needed to add a NOT NULL constraint to a table of 200 million rows – without taking any downtime. It’s not difficult (provided you are happy with a check constraint rather than a column definition.) alter table t1 add constraint t1_ck_colX_nn check (colX is not null) [...]

Posted 27 August 2010 § Uncategorized ‡ Comments Off °

DMCA

Some readers have noticed that a few links to my blog seem to be broken. Don’t panic, it’s not permanent it’s just the result of Don Burleson losing his temper. Let me start by telling you about DMCA, the “Digital Millenium Copyright Act”. DMCA is a mechanism designed to protect Internet service providers (ISPs) from [...]

Posted 26 August 2010 § Uncategorized ‡ Comments Off °

Index rebuilds

A couple of years ago I wrote about a poster on the OTN db forum who was puzzled by the fact that when he started rebuilding tables they got bigger.  (Bad luck, sometimes that’s what happens !) A few days ago a related question appeared: I rebuilt some indexes and my query got slower. (Bad [...]

Posted 24 August 2010 § Uncategorized ‡ Comments Off °

Cardinalilty One

I think anyone who has read Wolfgang Breitling’s material about the optimizer will be familiar with the concept of Cardinality Feedback and one particular detail that when Oracle gets a cardinality estimate of one for a “driving” table then there’s a good chance that the execution plan will go wrong. (That’s not rule, by the [...]

Posted 22 August 2010 § Uncategorized ‡ Comments Off °

Index Space

I’ve just been reminded of a thread on OTN which turned into a Q&A about index space usage and various related topics. On re-reading it, I decided it was too good to waste in the backwaters of OTN, so here’s a link to it. Index Space Utilization.

Posted 20 August 2010 § Uncategorized ‡ Comments Off °