Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: High DML Table - Suggestions??

Re: High DML Table - Suggestions??

From: Christian Trassens <>
Date: Wed, 22 Aug 2001 00:38:33 -0700
Message-ID: <>

Partition. Even on 7, obvious in this case would be partition views with maybe some changes in the app. Or maybe not depending the app.

Stripping. Oracle stripping and OS stripping.

Freelists. It seems that the app insert and query. Because of the inserts it is a perfect case of freelists. If you the database is on 8.1.6, you can change the freelists through alter table.

Reorg. If you could afford to throw the old data from the table to a backup or another database, would be great. In another database the users could query the old data, just like in the production one. Because sometimes Dev and the users prefer to leave the table growing and leaving old data behind and they don't admit that the data they use is only 10% of the table and the newest. I don't know maybe I am wrong because you said it is a customer one.

Try to keep Oracle means of mantaining referential integrity away from the table. I mean foreign keys or integrity controled by triggers. The less fk they need, less indexes need to be created. And those indexes always seem to be a nightmare when you use rule optimizer. Because they are choosen by Oracle and in most of the cases they don't have a good selectivity. What I am saying maybe it is a thing argue a lot. However I've seen contrary to popular opinion that in big tables it is better to control the referential intergrity through the app..

Partition indexes too.

Haven't you seen some ORA-1555 when you have a lot of users querying the table and adding data ?. Because for a main table like that in a place I've been, we implemented the table in a cluster.


Eng. Christian Trassens
Senior DBA
Systems Engineer
Phone : 541149816062

Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger Received on Wed Aug 22 2001 - 02:38:33 CDT

Original text of this message