Jonathan Lewis

The UKOUG’s big event has started, but I’mskipping the keynote to type this up. My agenda for today looks like this: 10:55 John King talkiing about XML 11:50 James Morle on Virtual Insanity 13:45 Julian Dyke at a round-table discussion on RAC and HA 14:50 Me, on how to write efficient SQL 16:00 Bryn Llewellyn on SQL in PL/SQL 17:10 Roybn [...]

I received an email recently which said: I have a question about Page 54 of Cost Based Oracle. 10g Update As with equalities, 10.1.0.4 suddenly changes what happens when you fall outside the low/high limits. The arithmetic, or picture, used for ranges outside the limits is just the same as the new mechanism we saw for equalities. I [...]

In one of those little coincidences that seem to happen so often a question came up on the comp.databases.oracle. server news group that has an answer in a blog note I’d written just a few of days earlier . The question was simply asking for a way of counting the number of rows in each [...]

You may have heard about “flash cache” for Oracle 11.2 and Exadata – the clever trick where the code can use SSD (solid state disc) for data that’s “fairly popular” but not quite popular enough to be kept in the data cache by the LRU. The code works to keep a copy of the more [...]

Here’s an interesting question raised by Uwe Hesse: “if you drop a table when someone else is querying it, what happens to their query?” It’s an interesting demonstration of how many features you have to consider before you reach a conclusion – and while you may start by assuming that the question is irrelevant to a [...]

A few weeks ago I wrote a note demonstrating the way in which Oracle’s strategy for hash partitioning is engineered to give an even data distribution when the number of partitions is a power of two. In one of the follow-up comments, Christo Kutrovsky pre-empted my planned follow-up by mentioning the hashing function ora_hash() that [...]

I’ve just discovered I’m on YouTube in an impromptu video interview I gave while I was at Oracle World this year. It’s only three minutes, and doesn’t have a lot of technical content, but here’s the URL. Footnote: Rumour has it that next year the event will be rebranded as My Oracle World – implemented entirely in Flash.   [...]

Every now and again I see a note on the OTN database forum, or Metalink, or the newsgroup asking why the time taken to run a query can vary so much between executions … even when the plan didn’t change (and the bind variable used as inputs didn’t change). Just off the top of my head, [...]

A few months ago Saibabu Devabhaktuni let me know of an interesting issue he had had with indexes misbehaving, and pointed me to a blog note he had written to describe it. In the note he supplies a test case where Oracle walks through hundreds of blocks on an index freelist to find a single [...]

… a few things about the use of index space to suprise your friends and amaze your colleagues: If you use “bigfile” tablespaces for your tables this can result in some indexes becoming more space-efficient than they would be otherwise. Creating tables in tablespaces built from multiple datafiles may cause some of their indexes to be less space-efficient [...]

From time to time I browse the comments that Akismet (the spam detector used by WordPress) has intercepted just to check that a useful comment hasn’t been unjustly labelled as spam; and a couple of days ago I was surprised to find that four comments (from the same person) had been marked as spam even [...]

California, Bulgaria (yes, I know it’s not in the USA), Virginia and Texas It’s nice after a long trip to get a few email messages from people who have heard you speak and enjoyed the experience – so thanks to the people who let me know what they thought of the seminars and presentations I’ve done [...]

People often create far more “foreign key” indexes than are needed – and any redundant index is a waste of several types of resources. I’ve just made some comments on an OTN thread about this topic, so rather than repeat the comments here I’ll just give you the URL. Update 5th Nov 2009: and here’s another [...]

Sorry, this isn’t a posting about efficient ways of getting the first 10 rows from a result set – it’s a little note about Oracle Open World. I don’t brag very often, but sometimes it’s hard to resist. I’ve just received an email about Openworld with the following content: Go to Oracle OpenWorld On Demand Get in on [...]

I’ve just jotted down a few notes about “log file sync” waits, “log file parallel write” waits, and the nologging option in response to a question on OTN about redo activity when creating a large index. The thread is probably worth reading.

If you’re into using Streams, you may want to take a look at the latest enhancement: XStreams. There’s a little overview, plus a couple of Java examples at the following links: Overview/FAQ Inbound streams data Outbound streans data

Btree indexes vs. Bitmap indexes – the critical difference A single B-tree index allows you to access a small amount of data very precisely. It is the combination of a subset of the available bitmap indexes that offers the same degree of precision. You should not be comparing the effectiveness of a bitmap index with the effectiveness of a b-tree [...]

Okay, so it’s not night-time, or even early evening, in some parts of the world – but somewhere it’s Friday night, and Friday night is quiz night. Now, most people are aware that the clustering_factor of an index measures the ordering (or, if you want to be slightly more accurate, the clustering) of the rows in [...]

It is fairly well-known that bitmap indexes are very dense structures that can behave badly if their underlying tables are subject to even fairly low levels of insert, update or delete activity. Problems include contention, space management and performance, and these problens have spawned a couple of well-known guidelines relating to bitmap indexes: Avoid concurrent modification [...]

Karen Morton has a few wise words to say about understanding vs. memorization. Definitely worth reading and understanding – and maybe even memorizing.