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
The Hashed Data Cache Threshold determines how much data should be available to a process for temporary caching of data to improve table join performance. The Hashed Data Cache will cache small tables or blocks of data that will be accessed repeatedly, but will not exceed the Hashed Data Cache Threshold.
The default Hashed Data Cache Threshold is 32MB, meaning that each query may use up to 32MB of memory to temporarily cache data.
The Hashed Data Cache Threshold is changed using the HDC_THRESHOLD option of the SELECT statement. The value used in this option is the number of megabytes of memory that should be allowed.
select ... with HDC_THRESHOLD=64
The Hashed Data Cache Threshold can also be disabled using the HDC_THRESHOLD option of the SELECT statement.
select ... with HDC_THRESHOLD=OFF
See also: