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.