Skip to Content
Former Member
May 20, 2014 at 01:49 PM

issue with clustered index


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

actn_flag char

o_code varchar

at_nbr char

m_val numeric

emplye_acct_ind char

mng_acct_ind char

rstrctn_ind char

fe_crtra_id int

w_code varchar

trns_type_code varchar

txt varchar

update_id char

update_dt datetime

yr_nbr numeric

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