Home » Server Options » Text & interMedia » delete from ctxsys.dr$delete - statement not using bind variables: how to contain of limit?
delete from ctxsys.dr$delete - statement not using bind variables: how to contain of limit? [message #689198] Tue, 10 October 2023 07:54
leon@ubeeo.nl
Messages: 1
Registered: October 2023
Junior Member
We are using OracleText for a quickfind option in our application.

The important part has been setup as follows:

...

ctx_ddl.create_preference('BO_QF_IDX_STO','BASIC_STORAGE');
 ctx_ddl.set_attribute('BO_QF_IDX_STO','I_TABLE_CLAUSE','tablespace WD_I2 storage (initial 50M)');
 ctx_ddl.set_attribute('BO_QF_IDX_STO','K_TABLE_CLAUSE','tablespace WD_I2 storage (initial 50M)');
 ctx_ddl.set_attribute('BO_QF_IDX_STO','R_TABLE_CLAUSE','tablespace WD_I2 storage (initial 50M) lob (data) store as (cache)');
 ctx_ddl.set_attribute('BO_QF_IDX_STO','N_TABLE_CLAUSE','tablespace WD_I2 storage (initial 50M)');
 ctx_ddl.set_attribute('BO_QF_IDX_STO','I_INDEX_CLAUSE','tablespace WD_I2 storage (initial 50M) compress 2');
 ctx_ddl.set_attribute('BO_QF_IDX_STO','P_TABLE_CLAUSE','tablespace WD_I2 storage (initial 50M)');
 ctx_ddl.set_attribute('BO_QF_IDX_STO','I_ROWID_INDEX_CLAUSE','tablespace WD_I2  storage (initial 50M)');
 ctx_ddl.set_attribute('BO_QF_IDX_STO','BIG_IO','YES');
 ctx_ddl.set_attribute('BO_QF_IDX_STO','SEPARATE_OFFSETS','T');
 ctx_ddl.set_attribute('BO_QF_IDX_STO','STAGE_ITAB','YES');
 ctx_ddl.set_attribute('BO_QF_IDX_STO','STAGE_ITAB_MAX_ROWS', 10000);
 ctx_ddl.set_attribute('BO_QF_IDX_STO','G_TABLE_CLAUSE','storage (buffer_pool keep)');
 ctx_ddl.set_attribute('BO_QF_IDX_STO','G_INDEX_CLAUSE','storage (buffer_pool keep)');

end;

create index bo_qf_idx
 on bo_qf (qf_match_string)
 indextype is ctxsys.context
 parameters('
   datastore       BO_QF_IDX_DST
   filter          BO_QF_IDX_FIL
   section group   BO_QF_IDX_SGP
   lexer           BO_QF_IDX_LEX
   wordlist        BO_QF_IDX_WDL
   stoplist        BO_QF_IDX_SPL
   storage         BO_QF_IDX_STO
   sync (on commit) nopopulate
 ');

Lately we seem to suffer from the following statement which is executed a lot:
delete from ctxsys.dr$delete where del_idx_id = :idxid   and del_ixp_id = :ixpid   and del_docid in (0000000);

000000 being a fixed number. Apart from the fact that I am personally blown away by the not use of bind variables in Oracle's own statement, we are wondering how we can force OracleText not to delete on submit, but do the deletion in bulk (less queries as it can process 800 in an in-statement).

Is the "submit" linked to "sync (on commit)" of "commit" of the original record being deleted?
Or force OracleText not to do a "delete" and "insert", but perform an "update" (see link below)?

Hope anybody can shred some light. Only thing I found so far:
- //community.oracle.com/mosc/discussion/4509686/delete-from-ctxsys-dr-delete-dont-use-binds

Kind regards,

Leon
Previous Topic: How to determine last tiime Content was indexed
Next Topic: Oracle Text provides easy Google-like searching using new query parser
Goto Forum:
  


Current Time: Thu Feb 22 04:33:24 CST 2024