Jonathan Lewis's picture

Index degeneration

There’s a thread on OTN that talks about a particular deletion job taking increasing amounts of time each time it is run.

It looks like an example where some thought needs to go into index maintenance and I’ve contributed a few comments to the thread – so this is a lazy link so that I don’t have to repeat myself on the blog.

Jonathan Lewis's picture

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 luck, sometimes that’s what happens – again!)

If you rebuild an index it’s physically different and its statistics are different. Plans can change and go slower because the index stats look sufficiently different; plans can stay the same and go slower because the index is physically different. 

I’ve added a couple of comments to the thread – there may still be some further mileage in it.

Jonathan Lewis's picture

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.

Jonathan Lewis's picture


Here’s an odd little bug (I think) in the optimizer that showed itself in a thread on the OTN database forum some time ago. The optimizer can choose an index which is NOT the cheapest index for satisfying a query against a single table. Here’s the demo – which I’ve run against using an 8KB block size, ASSM and system allocated extent sizes:

Jonathan Lewis's picture

Fragmentation 4

This note is part four of a four-part series, and covers Index fragmentation. The whole series is as follows  Introduction Disk and Tablespace Fragmentation Table Fragmentation Index Fragmentation – this bit 4. Index “fragmentation”. The multiple extent and ASSM “fragmentation” that I described in the previous about table fragmentaiton applies equally well to indexes, of course, [...]

Jonathan Lewis's picture

Index branches

Some time ago I published a little script that showed you how to read an index treedump and summarise the leaf block usage in the logical order of the index leaf blocks – allowing you to see fairly easily if the index had any areas where the blocks were poorly filled. Here’s another  way of looking [...]

Jonathan Lewis's picture

Cursor Sharing 3

Here’s a simple piece of code demonstrating an irritating problem. I’ve created a table, a function-based index, collected stats (without histograms), and then run a query that should use that index – but doesn’t. execute dbms_random.seed(0) create table t1 as with generator as ( select --+ materialize rownum id from dual connect by rownum <= [...]

Jonathan Lewis's picture

Index Rebuild ?

While searching on Metalink for clues about an oddity relating to an index rebuild, I came across Bug 6767655  – reported in and fixed in 11.2.  The problem is described as follows: When having two sessions running concurrently, one doing inserts to a partitioned table, and the other doing partition maintenance operations, there is [...]

Syndicate content