This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:indexes:options [2011/01/20 20:35] els |
admin:indexing:indexes:options [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ~~NOTOC~~ | ||
| + | |||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| Line 14: | Line 16: | ||
| Omnidex supports a variety of options that can be applied to indexes. Not every option can be used with every index type, so the tables below show the options and when they can be used. | Omnidex supports a variety of options that can be applied to indexes. Not every option can be used with every index type, so the tables below show the options and when they can be used. | ||
| - | | | Omnidex | QuickText | FullText | Custom | | + | === Index Options For Each Index Type === |
| - | |[[#keywording:KEYWORDING]] ^ | Implied | Implied | | | + | |
| - | |[[#case_sensitive:CASE_SENSITIVE]] | Default | | | | | + | This table shows the options that are available for each index type. Note that Custom Indexes can use all options, and so these indexes should be used to achieve specific combinations that are not allowed in the other types. In the table below, combinations that are shaded and contain a dash are disallowed. |
| - | |[[#case_insensitive:CASE_INSENSITIVE]] | | Default | Default | | | + | |
| - | |[[#proximity:PROXIMITY]] ^ ^ | Implied | | | + | |
| - | |[[#phonetic:PHONETIC]] ^ ^ ^ | | | + | |< 60% 40% 15% 15% 15% 15% >| |
| - | |[[#excluded_words:EXCLUDED_WORDS]] ^ | Implied | Implied | | | + | | | **Omnidex** | **QuickText** | **FullText** | **Custom** | |
| - | |[[#bitmap:BITMAP]] ^ ^ ^ | | | + | |**[[#keywording|KEYWORDING]]** ^ --- | Implied | Implied | OK | |
| - | |[[#standalone:STANDALONE]] ^ ^ ^ | | | + | |**[[#case_sensitive|CASE_SENSITIVE]]** | Implied | OK | OK | OK | |
| - | |[[#record_specific:RECORD_SPECIFIC]] ^ ^ ^ | | | + | |**[[#case_insensitive|CASE_INSENSITIVE]]** ^ --- | Default | Default | OK | |
| - | |[[#record_complex:RECORD_COMPLEX]] ^ ^ ^ | | | + | |**[[#proximity|PROXIMITY]]** ^ --- ^ --- | Implied | OK | |
| - | |[[#prejoin:PREJOIN table]] ^ ^ ^ | | | + | |**[[#phonetic|PHONETIC]]** ^ --- | OK | OK | OK | |
| - | |-------------------------------------|-------------|-------------|-------------|-------------| | + | |**[[#excluded_words|EXCLUDED_WORDS]]** ^ --- | Implied | Implied | OK | |
| + | |**[[#bitmap|BITMAP]]** | OK ^ --- ^ --- | OK | | ||
| + | |**[[#standalone|STANDALONE]]** | OK ^ --- ^ --- | OK | | ||
| + | |**[[#record_specific|RECORD_SPECIFIC]]** ^ --- ^ --- ^ --- | OK | | ||
| + | |**[[#record_complex|RECORD_COMPLEX]]** ^ --- ^ --- ^ --- | OK | | ||
| + | |**[[#prejoin_table|PREJOIN table]]** ^ --- ^ --- ^ --- | OK | | ||
| + | |||
| + | === Index Option Compatibility === | ||
| + | |||
| + | This table shows the compatibility of index options with each other. Some index options are mutually exclusive, and some options imply other options. In the table below, combinations that are marked with "Implied" mean that the option to the left implies the option on the top. Combinations that are shaded and contain a dash are disallowed. | ||
| + | |||
| + | |||
| + | |< 100% 18% 5% 7% 7% 7% 7% 7% 7% 7% 7% 7% 7% 7% >| | ||
| + | | | | **KW** | **CS** | **CI** | **PX** | **PH** | **EW** | **BM** | **SA** | **RS** | **RC** | **PJ** | | ||
| + | |**[[#keywording|KEYWORDING]]** | **KW** ^ --- | OK | Implied | OK | OK | Implied ^ --- ^ --- | OK | OK | OK | | ||
| + | |**[[#case_sensitive|CASE_SENSITIVE]]** | **CS** | OK ^ --- ^ --- | OK | OK | OK | OK | OK | OK | OK | OK | | ||
| + | |**[[#case_insensitive|CASE_INSENSITIVE]]**| **CI** | OK ^ --- ^ --- | OK | OK | OK | OK | OK | OK | OK | OK | | ||
| + | |**[[#proximity|PROXIMITY]]** | **PX** | Implied | OK | Implied ^ --- | OK | Implied ^ --- ^ --- ^ --- ^ --- ^ --- | | ||
| + | |**[[#phonetic|PHONETIC]]** | **PH** | OK | OK | OK | OK ^ --- | OK ^ --- | OK | OK | OK | OK | | ||
| + | |**[[#excluded_words|EXCLUDED_WORDS]]** | **EW** | OK | OK | OK | OK | OK ^ --- | OK | OK | OK | OK | OK | | ||
| + | |**[[#bitmap|BITMAP]]** | **BM** ^ --- | OK | OK ^ --- ^ --- | OK ^ --- | Implied ^ --- ^ --- ^ --- | | ||
| + | |**[[#standalone|STANDALONE]]** | **SA** ^ --- | OK | OK ^ --- | OK ^ --- | OK ^ --- ^ --- ^ --- ^ --- | | ||
| + | |**[[#record_specific|RECORD_SPECIFIC]]** | **RS** | OK | OK | OK ^ --- | OK | OK ^ --- ^ --- ^ --- ^ --- | OK | | ||
| + | |**[[#record_complex|RECORD_COMPLEX]]** | **RC** | OK | OK | OK ^ --- | OK | OK ^ --- ^ --- ^ --- ^ --- | OK | | ||
| + | |**[[#prejoin_table|PREJOIN table]]** | **PJ** | OK | OK | OK ^ --- | OK | OK ^ --- ^ --- | OK | OK ^ --- | | ||
| + | |||
| + | === Option Descriptions === | ||
| + | |||
| + | == KEYWORDING == | ||
| + | |||
| + | The KEYWORDING option causes the contents of the index to be parsed into keywords based on spaces and punctuation marks. The individual keywords are indexed and can be qualified separately in queries. For example, if a column containing "John Q. Doe" is indexed with KEYWORDING, it can be qualified using any of the following criteria: | ||
| + | * "John" | ||
| + | * "Doe" | ||
| + | * "John Doe" | ||
| + | * "Doe, John" | ||
| + | |||
| + | Unless overridden, the KEYWORDING option implies the CASE_INSENSITIVE and EXCLUDED_WORDS options. | ||
| + | |||
| + | == CASE_SENSITIVE == | ||
| + | |||
| + | The CASE_SENSITIVE option causes the contents of the index to be case-sensitive. This will require that criteria use the same combination of upper-case and lower-case letters as found in the data. | ||
| + | |||
| + | The CASE_SENSITIVE and CASE_INSENSITIVE options are mutually exclusive. | ||
| + | |||
| + | == CASE_INSENSITIVE == | ||
| + | |||
| + | The CASE_INSENSITIVE option causes the contents of the index to be case-insensitive. This allows the criteria to match the data regardless of the combinations of upper-case and lower-case letters. | ||
| + | |||
| + | The CASE_SENSITIVE and CASE_INSENSITIVE options are mutually exclusive. | ||
| + | |||
| + | == PROXIMITY == | ||
| + | |||
| + | The PROXIMITY option is similar to the KEYWORDING option; however, it also records the relative location of the keywords within the index. This allows the use of extended operators that compare the location of one keyword against another, such as the NEAR, BEFORE, and AFTER operators. The PROXIMITY option also enables the use of the $CONTEXT and $SCORE functions in the SQL Engine. Consult the article on [[admin:indexing:text:home|Omnidex Text]] for more information on these operators and functions. | ||
| + | |||
| + | The PROXIMITY option implies the KEYWORDING option, and unless overridden, implies the CASE_INSENSITIVE and EXCLUDED_WORDS options. | ||
| + | |||
| + | == PHONETIC == | ||
| + | |||
| + | The PHONETIC option causes the contents of the index to include the phonetic equivalent of the data, as well as the original data itself. The PHONETIC option is required to use the PHONETIC option in a $CONTAINS function. Consult the article on [[admin:indexing:text:home|Omnidex Text]] for more information on these operators and functions. | ||
| + | |||
| + | == EXCLUDED_WORDS == | ||
| + | |||
| + | The EXCLUDED_WORDS option causes columns containing only spaces, or in the case of binary data, containing only zeros, to be omitted from the indexes. | ||
| + | |||
| + | The EXCLUDED_WORDS option is implied by the KEYWORDING and PROXIMITY options. | ||
| + | |||
| + | == BITMAP == | ||
| + | |||
| + | The BITMAP option causes the index to use a bitmap storage format. This storage format improves qualification speed and saves disk space for low-cardinality data. This option is only appropriate for columns with less than 32 distinct values. | ||
| + | |||
| + | The BITMAP option implies the STANDALONE option. | ||
| + | |||
| + | == STANDALONE == | ||
| + | |||
| + | The STANDALONE option causes the index to be stored in a separate file. Indexes with the STANDALONE option can be built individually using the [[dev:sql:statements:update_indexes:home|UPDATE INDEXES]] statement. Non-textual indexes that are greater than 32 bytes will automatically use the STANDALONE option. Non-textual indexes include Omnidex Indexes, or any Custom Index that does not invoke the KEYWORDING or PROXIMITY option. | ||
| + | |||
| + | == RECORD_SPECIFIC == | ||
| + | |||
| + | The RECORD_SPECIFIC option is used in conjunction with the PREJOIN option to create a pre-joined index. The RECORD_SPECIFIC option indicates that the index in the pre-joined child table should record information about both the row and the parent. This provides a powerful ability to join between multiple children in a parent. At the same time, these indexes are more expensive to build and are slower in base qualifications. | ||
| + | |||
| + | The RECORD_SPECIFIC and RECORD_COMPLEX options are mutually exclusive. | ||
| + | |||
| + | == RECORD_COMPLEX == | ||
| + | |||
| + | The RECORD_COMPLEX option is used in conjunction with the PREJOIN option to create a pre-joined index. The RECORD_COMPLEX option indicates that the index in the pre-joined child table should record information about only the parent. This is valuable when a single column in a pre-joined child is used as criteria. This provides a powerful ability to join between a child and its parent. | ||
| + | The RECORD_SPECIFIC and RECORD_COMPLEX options are mutually exclusive. | ||
| - | === Omnidex Indexes === | + | == PREJOIN table == |
| + | The PREJOIN option is used to pre-join a RECORD_SPECIFIC or RECORD_COMPLEX index with a parent table. This is required for Prejoined Indexes. | ||
| ==== ==== | ==== ==== | ||