Search and optimization parameters

These settings affect the way that Texis will process the search. They include settings which change the meaning of the search, as well as how the search is performed.

defaultlike

Defines which sort of search should occur when a like or contains operator is in the query. The default setting of "like" behaves in the normal manner. Other settings that can be set are "like3", "likep", "liker" and "matches". In each case the like operator will act as if the specified operator had been used instead.

matchmode

Changes the behavior of the matches clause. The default behavior is to use underscore and percent as the single and multi-character character wildcards. Setting matchmode to 1 will change the wildcards to question-mark and asterisk.

predopttype

The Texis engine can reorder the where clause in an attempt to make it faster to evaluate. There are a number of ways this can be done; the predopttpye property controls the way it reorders. The values are 0 to not reorder, 1 to evaluate and first, 2 to evaluate or first. The default is 0.

ignorecase

Note: Deprecated; see stringcomparemode setting which supercedes this. Setting ignorecase to true will cause string comparisons (equals, sorting, etc.) in the SQL engine to ignore case, e.g. "A" will compare identical to "a". (This is distinct from text comparisons, e.g. the LIKE operator, which ignore case by default and are unaffected by ignorecase.) Note: This setting will also affect any indices that are built; the value set at index creation will be saved with the index and used whenever that index is used. Note: In versions prior to version 5.01.1208300000 20080415, the value of ignorecase must be explicitly set the same when an index is created, when it or its table is updated and when it is used in a search, or incorrect results and/or corrupt indexes may occur. In later versions, this is not necessary; the saved-at-index-creation value will automatically be used. In version 6 and later, this setting toggles the ignorecase flag of the stringcomparemode setting, which supercedes it.

textsearchmode

Sets the APICP textsearchmode property; see Vortex manual for details and important caveats. Added in version 6.

stringcomparemode

Sets the APICP stringcomparemode property; see Vortex manual for details and important caveats. Added in version 6.

tracemetamorph

Sets the tracemetamorph debug property; see Vortex manual for details. Added in version 7.00.1375225000 20130730.

tracerowfields

Sets the tracerowfields debug property; see Vortex manual for details. Added in version 7.02.1406754000 20140730.

tracekdbf

Sets the tracekdbf debug property; see Vortex manual for details.

tracekdbffile

Sets the tracekdbffile debug property; see Vortex manual for details.

kdbfiostats

Sets the kdbfiostats debug property; see Vortex manual for details.

btreecachesize

Index pages are cached in memory while the index is used. The size of the memory cache can be adjusted to improve performance. The default is 20, which means that 20 index pages can be cached. This can be increased to allow more pages to be cached in memory. This will only help performance if the pages will be accessed in random order, more than 20 will be accessed, and the same page is likely to be accessed at different times. This is most likely to occur in a join, when a large number of keys are looked up in the index. Increasing the size of the cache when not needed is likely to hurt performance, due to the extra overhead of managing a larger cache. The cache size should not be decreased below the default of 20, to allow room for all pages which might need to be accessed at the same time.

ramrows

When ordering large result sets, the data is initially ordered in memory, but if more than ramrows records are being ordered the disk will be used to conserve memory. This does slow down performance however. The default is 10000 rows. Setting ramrows to 0 will keep the data in memory.

ramlimit

ramlimit is an alternative to ramrows. Instead of limiting the number of records, the number of bytes of data in memory is capped. By default it is 0, which is unlimited. If both ramlimit and ramrows are set then the first limit to be met will trigger the use of disk.

bubble

Normally Texis will bubble results up from the index to the user. That is a matching record will be found in the index, returned to the user, then the next record found in the index, and so forth till the end of the query. This normally generates the first results as quickly as possible. By setting bubble to 0 the entire set of matching record handles will be read from the index first, and then each record processed from this list.

optimize,nooptimize

Enable or disable optimizations. The argument should be a comma separated list of optimizations that you want to enable or disable. The available optimizations are:

join

Optimize join table order. The default is enabled. When enabled Texis will arrange the order of the tables in the FROM clause to improve the performance of the join. This can be disabled if you believe that Texis is optimizing incorrectly. If it is disabled then Texis will process the tables in the left to right order, with the first table specified being the driving table. Added in version 02.06.927235551.

compoundindex

Allow the use of compound indexes to resolve searches. For example if you create an index on table (field1, field2), and then search where field1 = value and field2 = value, it will use the index to resolve both portions of this. When disabled it would only look for field1 in the index. Added in version 02.06.929026214.

countstar

Use any regular index to determine the number of records in the table. If disabled Texis will read each record in the table to count them. Added in version 02.06.929026214.

minimallocking

Controls whether the table will be locked when doing reads of records pointed to by the index used for the query. This is enabled by default, which means that read locks will not be used. This is the optimal setting for databases which are mostly read, with few writes and small records. Added in version 03.00

groupby

This setting is enabled by default and will cause the data to be read only once to perform a group by operation. The query should produce indentical results whether this is enabled or disabled, with the performance being the only difference. Added in version 03.00

faststats

When enabled, which is the default, and when the appopriate indexes exist Texis will try and resolve aggregate functions directly from the index that was used to perform the WHERE clause. Added in version 03.00

readlock

When enabled, which is the default, Texis will use readlocks more efficiently if there are records that are scanned, but don't match the query. Texis will hold the read lock until a matching record is found, rather than getting and releasing a read lock for every record read. If you are suffering from lock contention problems, with writes waiting, then this can be disabled, which will allow more opportunity for the write locks to be granted. This is not normally suggested, as the work required to grant and release the locks would typically negate the benefit. Added in version 03.00

analyze

When enabled, which is the default, Texis will analyze the query for which fields are needed. This can allow for more efficient query processing in most cases. If you are executing a lot of different SQL statements that are not helped by the analysis you can disable this. Added in version 03.00

skipahead

When enabled, which is the default, Texis will skipahead as efficiently as possible, typically used with the SKIP parameter in Vortex. If disabled Texis will perform full processing on each skipped record, and discard the record. Added in version 03.00

likewithnots

When enabled (default), LIKE/LIKEP-type searches with NOT sets (negated terms) are optimized for speed. Added in version 4.02.1041535107 Jan 2 2003.

shortcuts

When enabled (default), a fully-indexed LIKE/LIKEIN clause ORed with another fully-indexed LIKE/LIKEIN should not cause an unnecessary post-process for the LIKEs (and entire query). Added in version 4.03.1061229000 20030818 as optimization18; in version 7.06.1475000000 20160927, alias shortcuts added.

likehandled

When enabled (default), a fully-indexed LIKE/LIKEIN clause ORed with another fully-indexed non-LIKE/LIKEIN clause should not cause an unnecessary post-process for the LIKE (and entire query).

Also, linear and post-process LIKE/LIKEIN operations caused not by the Metamorph query itself, but by the presence of another ORed/ANDed clause, do not check allinear nor alpostproc when this optimization is disabled (i.e. they will perform the linear or post-process regardless of settings, silently). E.g. fully-indexed LIKE ORed with linear clause, or two fully-indexed LIKEs ANDed (where the first's results are under maxlinearrows), could cause linear search or post-processing, respectively, of an otherwise fully-indexable Metamorph query.

Added in version 7.06.1475014000 20160927.

indexbatchbuild

When enabled, indexes are built as a batch, i.e. the table is read-locked continuously. When disabled (the default), the table is read-locked intermittently if possible (e.g. Metamorph index), allowing table modifications to proceed even during index creation. A continuous read lock allows greater read buffering of the table, possibly increasing index build speed (especially on platforms with slow large-file lseek behavior), at the expense of delaying table updates until after the index is nearly built, which may be quite some time. Note that non-Metamorph indexes are always built with a continuous read lock - regardless of this setting - due to the nature of the index. Added in version 5.01.1177455498 20070424.

indexdataonlycheckpredicates

When enabled (the default), allows the index-data-only optimization to proceed even if the SELECT columns are renamed or altered in expressions. Previously, the columns had to be selected as-is with no renaming or expressions. Added in version 7.00.1369437000 20130524.

indexvirtualfields

When enabled (the default), attempts to reduce memory usage when indexing virtual fields (especially with large rows) by freeing certain buffers when no longer needed. Currently only applies to Metamorph and Metamorph inverted indexes. Added in version 6.00.1322890000 20111203.

Example: set nooptimize='minimallocking'

options,nooptions

Enable or disable certain options. The argument should be a comma separated list of options to enable or disable. All options are off by default. The available options are:

triggers

When on, disable the creation of triggers.

indexcache

Cache certain Metamorph index search results, so that an immediately following Metamorph query with the same WHERE clause might be able to re-use the index results without re-searching the index. E.g. may speed up a SELECT field1, field2, ... Metamorph query that follows a SELECT count(*) query with the same WHERE clause.

ignoremissingfields

Ignore missing fields during an INSERT or UPDATE, i.e. do not issue a message and fail the query if attempting to insert a non-existent field. This may be useful if a SQL INSERT statement is to be used against a table where some fields are optional and may not exist.

Example: set options='indexcache'

ignorenewlist

When processing a Metamorph query you can instruct Texis to ignore the unoptimized portion of a Metamorph index by issuing the SQL set ignorenewlist = 1;. If you have a continually changing dataset, and the index is frequently updated then the default of processing the unoptimized portion is probably correct. If the data tends to change in large batches, followed by a reoptimization of the index then the large batch can cause significant processing overhead. In that case it may be wise to enable the ignorenewlist option. If the option is enable then records that have been updated in the batch will not be found with Metamorph queries until the index has been optimized. Added in version 02.06.934400000.

indexwithin

How to use the Metamorph index when processing "within N" (w/N) LIKE-type queries. It is an integer combination of bit flags:

0x01

: Use index for w/N searches when withinmode is "char [span]"

0x02

: Use index for w/N searches when withinmode is "word [span]"

0x04

: Optimize within-chars window down

0x08

: Do not scale up intervening (non-query) words part of window to account for words matching multiple index expressions, which rarely occur; this reduces false (too wide) hits from the index. Also do not require post-processing if multiple index expressions. In rare cases valid hits may be missed if an intervening word does index-match multiply; the N value can simply be increased in the query to return these.

The default is 0xf in version 7.06.1525203000 20180501 and later, when support for 0x8 was also added. In version 5.01.1153865548 20060725 up to then, the default was 0x7. The setting was added in version 4.04.1075255999 20040127 with a default of 0.

wildoneword

Whether wildcard expressions in Metamorph queries span a single word only, i.e. for multi-substring wildcards. If 0 (false), the query "st*ion" matches "stallion" as well as "stuff an onion". If 1 (true), then "st*ion" only matches "stallion", and linear-dictionary index searches are possible (if enabled), because there are no multi-word matches to (erroneously) miss. Note: prior to version 5.01.1208472000 20080417, this setting did not apply to linear searches; linear or post-process searches may have experienced different behavior. The default is 1 in version 6 and later, 0 in version 5 and earlier. Added in version 4.03.1058230349 20030714.

wildsufmatch

Whether wildcard expressions in Metamorph queries suffix-match their trailing substrings to the end of words. If 0 (false), the query "*so" matches "also" as well as "absolute". If 1 (true), then "*so" only matches "also". Affects what terms are matched during linear-dictionary index searches. Note: prior to version 5.01.1208472000 20080417, this setting did not apply to linear searches; linear or post-process searches may have experienced different behavior. The default is 1 in version 6 and later, 0 in version 5 and earlier. Added in version 4.03.1058230349 20030714.

wildsingle

An alias for setting wildoneword and wildsufmatch together, which is usually desired. Added in version 4.03.1058230349 20030714.

allineardict

Whether to allow linear-dictionary Metamorph index searches. Normally a Metamorph query term is either binary-index searchable (fastest), or else must be linear-table searched (slowest). However, certain terms, while not binary-index searchable, can be linear-dictionary searched in the index, which is slower than binary-index, yet faster than linear-table search. Examples include leading-prefix wildcards such as "*tion". The default is 0 (false), since query protection is enabled by default. Note that wildsingle should typically be set true so that wildcard syntax is more likely to be linear-dictionary searchable. Added in version 4.03.1058230349 20030714.

indexminsublen

The minimum number of characters that a Metamorph index word expression must match in a query term, in order for the term to utilize the index. A term with fewer than indexminsublen indexable characters is assumed to potentially match too many words in the index for an index search to be more worthwhile/faster than a linear-table search.

For binary-index searchable terms, indexminsublen is tested against the minimum prefix length; e.g. for query "test.#@" the length tested is 4 (assuming default index word expression of "\alnum{2,99}"). For linear-dictionary index searches, the length tested is the total of all non-wildcard characters; e.g. for query "ab*cd*ef" the length tested is 6.

The default for indexminsublen is 2. Added in version 4.03.1058230349 20030714. Note that the query - regardless of index or linear search - must also pass the qminprelen setting.

dropwordmode

How to remove words from a query set when too many are present (qmaxsetwords or qmaxwords exceeded) in an index search, e.g. for a wildcard term. The possible values are 0 to retain suffixes and most common words up to the word limit, or 1 to drop the entire term. The default is 0. Added in version 3.00.947633136 20000111.

metamorphstrlstmode

How to convert a strlst Metamorph query (perhaps generated by Vortex arrayconvert) to a regular string Metamorph query. For example, for the strlst query composed of the 3 strings "one", "two", and "bear arms", the various modes would convert as follows:

• allwords Space-separate each string, e.g. "one two bear arms".

• anywords Space-separate each string and append "@0''', e.g. `one two bear arms @0".

• allphrases Space-separate and double-quote each string, e.g. ""one" "two" "bear arms"".

• anywords Space-separate and double-quote each string, and append "@0''', e.g. `"one" "two" "bear arms" @0".

• equivlist Make the string list into a parenthetical comma-separated list, e.g. "(one,two,bear arms)".

The default is equivlist. Added in version 5.01.1225240000 20081028. See also the varchartostrlstsep setting (here), which affects conversion of varchar to strlst in other contexts.

compatibilityversion

Sets the Texis compatibility version - the version to attempt to behave as - to the given string, which is a Texis version of the form "major[.minor[.release]]", where major is a major version integer, minor is a minor version integer, and release is a release integer. Added in version 7. See the <vxcp compatibilityversion> setting in Vortex for details. See also the [Texis] Compatibility Version setting (here) in texis.ini, which the compatibilityversion setting defaults to.

failifincompatible

Whenever set nonzero/true, and the most recent compatibilityversion setting attempt failed, then all future SQL statements will fail with an error message. Since there is no conditional ("if") statement in SQL, this allows a SQL script to essentially abort if it tries to set a Texis compatibility version that is unsupported, rather than continue with possibly undesired side effects. Added in version 7. See also <vxcp compatibilityversion> in Vortex, which obviates the need for this setting, as it has a checkable error return.

groupbymem

When set nonzero/true (the default), try to minimize memory usage during GROUP BY/DISTINCT operations (e.g. when using an index and sorting is not needed). Added in version 7.00.1370039228 20130531.

legacyversion7orderbyrank

If on, an ORDER BY $rank (or $rank-containing expression) uses legacy version 7 behavior, i.e. typically orders in numerically descending order, but may change to ascending (and have other idiosyncrasies) depending on index, expression and DESC flag use. If disabled, such ORDER BYs are consistent with others: numerically ascending unless DESC flag given (which would typically be given, to maintain descending-numerical-rank order).

The default is the value of the [Texis] Legacy Version 7 Order By Rank setting (here) in conf/texis.ini, which is off by default with compatibilityversion 8 and later, on in earlier versions (compatibilityversion defaults to Texis Version). Added in version 7.06.1508871000 20171024.

Note that this setting may be removed in a future release, as its enabled behavior is deprecated. Its existence is only to ease transition of old code when upgrading to Texis version 8, and thus should only be used temporarily. Old code should be updated to reflect version 8 default behavior - and this setting removed - soon after upgrading.