I've following table with unique clustered composite PK on (o_code, at_nbr, yr_nbr, update_dt) columns and index of height 5.
Total No of rows in table 275225447
The issue is inserts on this table are slow. This could be because of unique clustered composite PK. I’ve tried dropping the cluster unique index and tried for normal unique b*tree index. Insert have been improved, but the selects on this table were highly impacted. I think the slow inserts is because of when the row is getting inserted at mid off unique clustered composite PK the oracle has to re-align all the blocks in index structure (my guess). Is there any way to improve the performance the inserts in this situation? Any suggestion on re-design of the table is much appreciated.
Table is non-partitioned table.
There is no sequence that is generating surrogate keys. Table didn’t referred by in FK’s. (No child tables)
P.S. inserts are being performed by the trigger whenever underlying table is updated/inserted.
Version : Adaptive Server Enterprise/15.0.3/EBF 17770 ESD#4/P/x86_64/Enterprise Linux/ase1503/2768/64-bit/FBO/Thu Aug 26 09:54:27 2010