SAP Sybase IQ chooses one of three algorithms to process delete operations on columns with a WD (Word) index.
Small delete provides optimal performance when the rows deleted contain few distinct words, so that not many WD pages need to be visited. The WD small delete algorithm performs an ordered access to the WD. 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.
Mid delete for WD is a variation of WD small delete, and is useful under the same conditions as small delete, that is, when the rows deleted contain few distinct words. Mid delete for WD sorts only words in the records to delete. This sort is parallel, with parallelism limited by the number of words and CPU threads available. For Word index, the mid delete method is generally faster than small delete.
Large delete 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 WD 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 WD delete cost model considers many factors including I/O costs, CPU costs, available resources, index metadata, and parallelism.
You can use the WD_DELETE_METHOD database option to control WD delete performance.
The value of the parameter specified with the WD_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 WD_DELETE_METHOD database option, see “WD_DELETE_METHOD option” in “Database Options” of Reference: Statements and Options.