Slow Performance and Indices

Dear Oracle Gurus:

Please help me with a performance problem. My application has a table that stores temporary data from updates in progress. This temporary data contains changes (adds, deletes, updates) that will be applied to the table with the permanent data. The process that applies the updates runs fine in the morning, but as the day progresses, the process gets slower and slower until it just hangs. Any queries to the temporary data table get very slow. If I remake the index on the temporary data table, queries (and processing) speed up to the expected level.

The data in this temporary data table is very volatile; thousands of rows are added and deleted constantly. My conclusion is that the index is getting so unbalanced that it becomes useless. I tried running without the index, but the performance is even worse.

Recreating the index during the day is not an option; our application must be up continously and this particular data must be accessible at all times.

Why is this happening? Has anyone else seen this? Our database server is an HP-UX (Operating system A.09.00) running RDBMS V6.0.37.3.1.

Please respond via e-mail; I don't get to read this group often. Thanks,
Marianne G. Pittorino
AT&T
mpittorino_at_attmail.com Received on Tue Nov 23 1993 - 22:33:40 CET