This is an old revision of the document!
Omnidex has several threshold settings that control when optimizations take place. Thresholds are needed because not all queries should be optimized using Omnidex indexes. Sometimes, indexing can even slow down a query.
The Qualification Threshold determines when indexes are used to process criteria and retrieve data. When the number of rows qualified by the Omnidex exceeds a percentage of the rows in the table, it is no longer cost-effective to use indexes to retrieve the rows. This is because the underlying database will fetch a block of adjacent rows off the disk drive each time any of the rows is requested. Depending on the size of the row, each fetch may return 100 or more rows. This means that once you want more than 1% of the rows in the database, it becomes more efficient to perform a sequential table scan rather than using indexes.
The default Qualification Threshold is 1%, meaning that Omnidex indexes will not be used to retrieve more than 1% of the rows in the table. If special indexing capabilities are being used, such as text searches, phonetic searches or other Omnidex features, the Qualification Threshold will be disregarded since the search cannot be accomplished without the indexes.
Note that the Qualification Threshold option is not considered when the qualified rows are to be aggregated using Omnidex indexes, or when the qualified rows in that table will be applied as criteria to an Omnidex index in another table.
The Qualification Threshold is changed using the QUALIFICATION_THRESHOLD option of the SELECT statement.
select ... with QUALIFICATION_THRESHOLD=2.0
The Qualification Threshold can also be disabled using the QUALIFICATION_THRESHOLD option of the SELECT statement.
select ... with QUALIFICATION_THRESHOLD=OFF
The Aggregation Threshold determines when indexes are used to aggregate data. When the number of rows qualified by the Omnidex falls below a percentage of the rows in the table, it is no longer cost-effective to use indexes to aggregate the rows. There is a base cost to scanning Omnidex indexes for aggregating data, and if few enough rows are to be aggregated, it is simply faster to fetch the rows and aggregate them without the use of indexes.
The default Aggregation Threshold is 0.01%, meaning that Omnidex indexes will not be used to aggregate less than 0.01% of the rows in the table.
The Aggregation Threshold is changed using the AGGREGATION_THRESHOLD option of the SELECT statement.
select ... with AGGREGATION_THRESHOLD=0.02
The Qualification Threshold can also be disabled using the QUALIFICATION_THRESHOLD option of the SELECT statement.
select ... with AGGREGATION_THRESHOLD=OFF
See also: