Re: Forever growing indexes problem

<stan brown posted:>

        This *can't* be correct. If it is how do all online systems that run 24 hours a day 7 days a week *ever* work using Oracle?

        Let me rephrase the problem. We have tables that we put data into every 15 seconds. We have a program the periodicly removes this data,therefore the *tables* do not! fill up. However the indexes do,This really can't be right. I must be doing something wrong.

<and dave replies:>

You're not doing anything wrong! The index is a tree; the table is not. If you delete a row of table, you leave a hole which could be filled with any new row; but if you delete a leaf of the index tree, you cannot just put anything in its place, because the tree structure says something important about how you search for the keys. Therefore indexes do not refill holes the way you expect.

Systems with 24-hour uptime characterize tables and indexes as either read-only, mostly-read, or mostly-write, with respect to the application. If a table and its indexes are heavily restructured due to inserts, deletes, and more inserts, some provision for excess growth is generally made at design time.

It may be a deficiency of the basic design of oracle's data structure, but dba's in such situations know that periodic maintenance of mostly-write segments calls for export-drop-import of the tables (and therefore the indexes, too).

When you get into the 100's-of-gigabyte range, this is obviously a major problem. The design of such systems takes into account the volatility of the data so that restructuring can be avoided. The solution is often "divide and conquer": that is, separate tables according to certain major primary keys, instead of one huge table. Then, during a load phase, you create a new table; during a delete phase, you drop the table. This avoids the unpredictable expansion you've been burdened with.

You may surmise that divide-and-conquer calls for dynamic sql methods because the table names reflect the key values.

This may be overkill for your application. The only alternative I can think of is: plan ahead and allocate extra space according to your previous experience with the data; or perform regular index re-creations. PS.: it's generally faster to drop the index when loading a bunch of rows (i'm ignoring direct path loading here).

-- 
Dave Mausner / Sr Consultant / Braun Technology Group / Chicago, IL USA
No opinions have been expressed.              Motto: "Show me the code"