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;
Thanks.