Index becomes slower over the day

From: Martin Klier <>
Date: Wed, 14 Jan 2009 08:49:43 +0100
Message-ID: <>

Dear list,

I've got the situation, that using one particular index is fast and becomes slower. In detail, the index becomes slower over the day/week, and since it's an OLTP system, this is annoying. (CPU and IO cost are high). The index always stays "VALID".

Rebuilding it immediately speeds up the whole case by factors of ten, without any change in the execution plan before or after. But I can't rebuild it on an hourly basis, of course!

Now a theory: Maybe the tree structure of the index becomes less efficient over time by inserting linear sequences (inside: tendency to a binary list, not a real tree structure). Is this imaginable?

If yes, a theory to solve it: Would a reverse index be useful to help the index to maintain a "better" tree structure?

Tom Kyte and others are writing about the use of the reverse indexing in case of concurrently written blocks, but I can find nothing looking really useful for my case.

Thanks in advance. I just know basics about indexes, so please be patient. :)

Best regards
Martin Klier

Usn's IT Blog for Linux, Oracle, Asterisk

-- Received on Wed Jan 14 2009 - 01:49:43 CST

Original text of this message