Re: Delete/Insert and high number of concurrent users

On 19 Sep 2004 09:25:16 -0700, gautrak_at_yahoo.com (Gautrak) wrote:

>Hi,
>Oracle version : 9.2
>
>A transaction, in addition to other statements, involves first
>deleting all records belonging to that userid and then insert fresh
>set from another table.
>The database must be able to support large number of users, each with
>own userid, performing this transaction.
>
>I believe this is not a good design but have no control on this part.
>
>To ensure that these delete statements do not result in any
>bottlenecks, what should be the focus areas.
>
>Thanks and regards,
>Gautrak

Hi Gautrak,

At first, you should be concerned about the inserts as well, not only the deletes.

At second, one crucial thing about such applications is scalability.

If your users are deleting/inserting from/into the same table (not clear from your post), partitioning seems to be a must. Either range partitioning or list partitioning, and probably partitioned by userid.   

Rightly done this will ensure an equal distribution of the transactions over the partitions, avoiding inpredictable many users fighting to access the same blocks.

Also consider the possible indexing on this partitioned table, e.g. whether to choose global or local indexes.

• Kenneth Koenraadt