Create a text index on a large volume of data, and then
run incremental updates every other hour to index the modified data.
About this task
Create a text index on a table, where the column contains
300-GB data content. Modify the table by using SQL insert, updates
or deletes. Processing might take almost a day in total.
When
workload is high during daytime, there is the concern that interrupts
during the processing might cause loss of work and so a longer overall
run time is used for the safety of more frequent commits. So, backups
are often scheduled in the evenings using commit batches.
Procedure
- Use the CREATE INDEX FOR TEXT operation
to create an index.
Use a three hour
COMMITCYCLES for
a six hour time window, starting at midnight. The
LOGTYPE index
configuration option is set to use the default
BASIC option.
create index myidx1 for text on mytable(comment1)
update frequency d(*) h(0) m(0)
index configuration( updateautocommit 3, committype hours, commitcycles 2)
- After the initial update is complete, adjust the schedule
frequency to run incremental updates every other hour.
Set
the
UPDATEAUTOCOMMIT setting to 0 to avoid the
related memory consumption, if the number of changes is comparatively
small.
- Issue either one of the following sample ALTER INDEX
FOR TEXT commands:
Alter index myidx1 for text
update frequency d(*) h(0,2,4,6,8,10,12,14,16,18,20,22) m(0)
index configuration (updateautocommit 0)
Alter index myidx1 for text
update frequency d(*) h(0,2,4,6,8,10,12,14,16,18,20,22) m(0)
index configuration (updateautocommit 0, committype hours, commitcycles 0)