Oracle Rolls Out Text Index Strategy
March 7, 2013
Oracle’s support of locally partitioned indexes has created a need for users to be able to split those indexes and rebuild them in a timely manner. How do you rebuild an index without making your application unavailable for the entire time?
Prsync’s look into the maintenance disadvantages and subsequent problem solving by Oracle in “Partition Maintenance and Oracle Text Indexes” gives us a look at something new; a “Without Validation” and “Split Partition” features. These options offer a way to rebuild indexes without checking each line-by-line first.
“That solves the problem, but it’s rather heavy-handed. So instead we need to institute some kind of “change management”. There are doubtless several ways to achieve this, but I’ve done it by creating triggers which monitor any updates or inserts on the base table, and copy them to a temporary “staging” table. These transactions can then be copied back to the main table after the partition split or merge is complete, and the index sync’d in the normal way.”
So now that there is a solution, but, by avoiding the need for a system to check every partition key value to make sure the row is going to the correct partition, there is need for extra care when using the without validation feature.
It’s a long needed saving grace that will save time and ultimately money by getting apps back up and running in a more efficient manner but there is no substitute for attention to detail. For a more in-depth look at the process we suggest heading over to prsync.
Leslie Radcliff, March 07, 2013