Index becomes slower over the day

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 http://www.usn-it.de

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jan 14 2009 - 01:49:43 CST