issue with clustered index

[Former Member]

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

[former_member188958]

Inserts into an ASE index do not have to realign all the blocks in the index structure.  They will occassionally cause the current leaf page to split.  That can on rare occasion cause the parent page to fill, causing it to split.  On extremely rare occasions, that fill cause it's parent to split.  etc.   At most 5 splits with a 5 level index, with most inserts not requiring any.

Are you saying that inserts into this table are done by a trigger on another table?

Or is it that inserts into this table fire a trigger that does other work?

[Former Member]

Bret,

Thanks for the reply.

Are you saying that inserts into this table are done by a trigger on another table?

yes..

Trigger is on the different table, let's say on 'T' and inserts are happening 'T_Audit' through trigger. So whenever inserts/updates happening on T table then corresponding rows will get inserted T_Audit table.

When I disable the trigger and then execute the update or insert(s) on T, updates/inserts getting executing quickly. Or create a test table(let's say 'T_Audit_try') which is of same structure of 'T_Audit' with no data and point trigger to 'T_Audit_try' updates/inserts getting executed it's getting executing quickly.

here is the trigger code.

----

CREATE TRIGGER dbo.trigger1

ON dbo.T1

for insert, update

AS

BEGIN

     if @@rowcount = 0 return

    if exists (select 1 from inserted)

    begin

        insert into dbo.T_Audit

        (actn_flag,o_code,at_nbr,m_val,emplye_account_ind ,mng_account_ind,rstrctn_ind,f_crtra_id,w_code,trnsfr_type_code,txt,update_dt,update_id,yr_nbr)

        select

            case when exists (select 1 from deleted) then 'U' else 'I' end,  o_code,at_nbr,m_val,emplye_account_ind ,mng_account_ind,rstrctn_ind,f_crtra_id,w_code,trnsfr_type_code,txt,update_dt,update_id,yr_nbr

        from inserted

    end

END