| 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
