A reader, April 28, 2023 - 5:12 am UTC
I was referring to this memory parameter:
CREATE INDEX doc_idx on docs(document) indextype is ctxsys.context
FILTER BY category, author
ORDER BY pub_date desc, docid
PARAMETERS ('memory 500M');
I have kind of figured out the problem in my case. My MV has three cols viz. Record_ID (base table record id), ROW_ID(base table record's row id) and Version(an incrementing integer value). The index is built on Version col.
Suppose my MV has a record (Record_id =123, Row_id= ABC123, Version=1) and this record is already synced in the index. If I am creating a new record in my base table, the mv is doing a delete insert for the record_id = 123 thus triggering the re-syncing of record_id 123. Just to add my base table is partitioned and has row movement enabled. I thought the row movement might be causing this reinsert for record_id 123 but the rowid after reinsert for the record is still ABC123 which means the row is not moved. I failed to understand why MV is doing a delete and insert for an existing refreshed record.
April 28, 2023 - 10:14 am UTC
Glad you figured it out.
As the docs say about MEMORY:
The memsize parameter specifies the amount of memory Oracle Text uses for indexing before flushing the index to disk. Specifying a large amount memory improves indexing performance because there are fewer I/O operations and improves query performance and maintenance, because there is less fragmentation.
Specifying smaller amounts of memory increases disk I/O and index fragmentation, but might be useful when run-time memory is scarce.
A reader, April 28, 2023 - 3:09 pm UTC
Could you please let me know why the MV is doing a delete insert for an existing record whenever a record is created in the base table.
May 01, 2023 - 3:19 pm UTC
Not sure what you mean.
I *suspect* you're referring to the refresh, which is handling everything that *might* be in the MV log. That *could* be deletes, it *could* be updates as well as inserts.
Hence the operations we do.