Sybase IQ chooses one of two algorithms to process delete operations on columns with a TEXT index:
Small delete Small delete for TEXT provides optimal performance when the rows deleted contain few distinct words, so that not many TEXT pages need to be visited. The TEXT small delete algorithm performs an ordered access to the TEXT. Worst case I/O is bounded by the number of index pages. Small delete incorporates the cost of sorting the words and record IDs in the records to delete.
Large delete Large delete for TEXT provides optimal performance when the rows deleted contain a large number of distinct words, and therefore need to visit a large number of “groups” in the index. The large delete scans the TEXT in order, until all rows are deleted. Worst case I/O is bounded by the number of index pages. Large delete is parallel, but parallelism is limited by the internal structure of the index and the distribution of groups from which to delete.
The TEXT delete cost model considers many factors including I/O costs, CPU costs, available resources, index metadata, and parallelism.
You can use the TEXT_DELETE_METHOD database option to control TEXT delete performance.
The value of the parameter specified with the TEXT_DELETE_METHOD option forces the use of the specified delete algorithm as follows:
0 = Mid or large delete as selected by the cost model
1 = Small delete
2 = Large delete
3 = Mid delete
For more information on the TEXT_DELETE_METHOD database option, see “TEXT_DELETE_METHOD option” in Chapter 2, “Text Indexes and Text Configuration Objects” of Unstructured Data Analytics in Sybase IQ.
