using trigger created by CTXCAT index makes the update takes too long

User_5VCW3

Dear all,

I have to update a table but below update query takes 50 sec , is there any other way to enhance this update statement.

mcdoc_recherche t set rdcsearch = 'je etrdémontre' where rdccleintref = 244546 and stamnetabref = 'DOC'

Plan hash value: 1817789086

 

------------------------------------------------------------------------------------------------

| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time   |

------------------------------------------------------------------------------------------------

|  0 | UPDATE STATEMENT       |         |   1 |  79 |   1  (0)| 00:00:01 |

|  1 | UPDATE           | MCDOC_RECHERCHE |    |    |      |     |

|  2 |  TABLE ACCESS BY INDEX ROWID| MCDOC_RECHERCHE |   1 |  79 |   1  (0)| 00:00:01 |

|* 3 |  INDEX UNIQUE SCAN     | MC_RDC_U04   |   1 |    |   1  (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

  3 - access("RDCCLEINTREF"=244546 AND "STAMNETABREF"='DOC')

There is a trigger DR$MC_RDC_C01TC that might be the problem of this slowness

create or replace trigger "UQAMPF"."DR$MC_RDC_C01TC" after insert or update on "UQAMPF"."MCDOC_RECHERCHE" for each row declare  reindex boolean := FALSE;  updop  boolean := FALSE; begin  ctxsys.drvdml.c_updtab.delete;  ctxsys.drvdml.c_numtab.delete;  ctxsys.drvdml.c_vctab.delete;  ctxsys.drvdml.c_rowid := :new.rowid;  if (inserting or updating('RDCSEARCH') or    :new."RDCSEARCH" <> :old."RDCSEARCH") then   reindex := TRUE;   updop := (not inserting);   ctxsys.drvdml.c_text_vc2 := :new."RDCSEARCH";  end if;  ctxsys.drvdml.ctxcat_dml('UQAMPF','MC_RDC_C01', reindex, updop); end;

The trigger is automatically created as soon as we create the index , I have tested in dev :

trigger enabled : 1,623 second

trigger disabled:0,053 second

I'm just wondering do we have to call this trigger or can we just disable it in Oracle Text context?

index creation:

CREATE INDEX "UQAMPF"."MC_RDC_C01" ON "UQAMPF"."MCDOC_RECHERCHE" ("RDCSEARCH") 

  INDEXTYPE IS "CTXSYS"."CTXCAT" PARAMETERS ('lexer sof_lexer wordlist sof_WDL stoplist UQ_VIDE');

objects created as a result of the index creation

SELECT object_type, object_name

FROM  user_objects

WHERE object_name LIKE '%MC_RDC_C01%'

TABLE DR$MC_RDC_C01$I

INDEX DR$MC_RDC_C01$R

INDEX DR$MC_RDC_C01$X

TRIGGER DR$MC_RDC_C01TC

INDEX MC_RDC_C01

As of for the trigger:

trigger "UQAMPF"."DR$MC_RDC_C01TC" 
  after insert or update on "UQAMPF"."MCDOC_RECHERCHE" 
declare   
  reindex boolean := FALSE;   
  updop   boolean := FALSE; 
begin   
  ctxsys.drvdml.c_updtab.delete;   
  ctxsys.drvdml.c_numtab.delete;
  ctxsys.drvdml.c_vctab.delete;   
  ctxsys.drvdml.c_rowid := :new.rowid;   
  if (inserting or updating ('RDCSEARCH') 
      or :new."RDCSEARCH" <> :old."RDCSEARCH") 
  then     
    reindex := TRUE;     
    updop := (not inserting);     
    ctxsys.drvdml.c_text_vc2 := :new."RDCSEARCH";   
  end if;   
  ctxsys.drvdml.ctxcat_dml ('UQAMPF','MC_RDC_C01', reindex, updop); 
end;

The trigger is created automatically when I create an index of type conctext.ctxcat, and if I drop the trigger the index won't be maintained. If it's taking that long to do that update

I don't know if it's possible to delay re-synching a ctxcat index or define it with manual re-sync

Any suggestions to fix the issue?

Thanks.