Indexing Options

Omnidex indexing options can be divided into 3 main groups:

• Database-level indexing options apply to the entire index installation.
• Table-level indexing options apply to a specific table. These options over-ride the database-level options.
• Index-level indexing options apply to a specific column. These options over-ride table-level and database-level options.

Database-Level Indexing Options

Indexes installed at the database-level will apply to all indexes installed on this database.

T - TRANSLATE - Use a custom Translation Table for translating 8-bit extended ASCII characters. The translation table will be applied to all Omnidex indexes that are NOT installed with the ;NT (No Translate) option. To disable translation of 8-bit characters for any column, install the column with the ;NT - No Translate option. The translation table will be applied to all columns except those installed with the ;NT (No Translate) option. More Info

X - XCLUDE - to prevent words contained in an Excluded Words List from being indexed. The excluded words list will be applied to all Omnidex indexes that are NOT installed with the ;NE (No Exclude) option. To disable the excluded words list for any index, install the index with the ;NE - No Exclude option. The excluded words list will be applied to all indexes except those installed with the No Exclude option. More Info

Effective in version 4.0, No Translate and No Exclude are the default MDK indexing options.

Table-Level Indexing Options

As of Omnidex version 4, there is no need for any table-level indexing options. By default, all tables are installed with ;TRR (see below). The ;TRR option is still supported for backward compatibility but is not necessary.

TRR - Transparency with Rowid - The ;TRR option creates a 32-bit, well-behaved ODXID for each row in the table, and a cross-reference file to link these ODXIDs with the data.

Index-Level Indexing Options

BI | BM | CI | DI | EX | Gn | KW | NE | NP | NT | RC | SX | Type=t | UK

;BI - Batch Indexing

The Batch Indexing (;BI) option disables the real-time updating of indexes as rows are added, deleted or updated. Batch Indexed indexes are updated only during a B or an INDEX operation.

The Batch Indexing option eliminates the overhead associated with updating indexes in real time when adding, deleting, or updating rows. For example, if data is entered during business hours, when online response is critical, it might be beneficial to install Batch Indexing on indexes that need not reflect the most current data, like a YTD (year to date) index in an ACCOUNTING table. Then a batch process could be run at night or on a weekend, when online response time is less critical, to update Batch Indexed indexes.

Although there are other ways to prevent real-time indexing, the Batch Indexing option gives the added benefit of affecting only selected indexes.

Install Batch Indexing only on indexes that need not reflect the most current data.

;BM - Bitmap Indexing

Bitmap indexing stores the Omnidex MDK index as a bitmap. Used on low cardinality data of less than 30 unique values with well behaved Omnidex IDs. Ideal for Data Warehouse applications that do not need real time indexing.

A bitmap index will be created for each unique value in the column installed with the ;BM option, and each bitmap index will contain a bit for each row in the table. Therefore, the table must have a well-behaved rowid to efficiently utilize bitmap indexing.

• Using the ;BM option will automatically invoke the DI, BI.
• Used only for MDK indexing.
• Most beneficial on tables with more than 100,000 rows

Because the Omnidex IDs must be a 32-bit integer, bitmap indexing can only be used on parent tables and DR child tables. A prejoined child table has a 64-bit integer ID and therefore cannot use bitmap indexing.

;CI - Case Insensitivity

The CI option causes text searches on the column to be case insensitive. This is most useful for non-parsed MDK fields like a two-digit state field and ASK indexed fields for sorting.

It is not necessary to install ;CI on an MDK indexed field installed with ;KW because case insensitivity is inherent with keyword indexed fields.

;DI - Distributed Indexes

Distributed Indexing causes each MDK index to be stored in a separate physical file instead of a single physical file.

• DI indexing implies the ;NP option and no grouping; that is, ;DI cannot be used in conjunction with ;Gn.
• Distributed Indexes can be up to 255 bytes where normally a non-parsed MDK Index can be only 32 bytes long.
• DI keys work in parents and DR children, but not in RS children.
• Distributed Indexing applies to only MDK Indexing.
• Most useful on very large databases where the Omnidex indexes hit an operating system limit.

Use the Distributed indexing (;DI) option for the ability to exceed the 2 (or 4) Gbyte file size limit on operating systems with this file size limitation.

Use the Distributed indexing (;DI) option to utilize the efficiencies of multiple CPUs to reduce indexing time.

When indexing Distributed Indexes (;DI), use the Build @@[n] or @[n] option where n ranges from 1 to 16 and represents the number of Distributed Indexes to process per pass through the data. For example, if eight Distributed Indexes are specified and the @@ option is used (where n is not specified), then the index Build process would require eight passes through the internal extract file used for indexing. If the Build option is specified as @@n (where n is 4), then only two passes through the extract file are needed.

;EX - External Document Indexing

External Document Indexing is used to parse and index the contents of text-based documents that are external to a database. HTML files, ASCII files, Word documents, Excel Spreadsheets, and many other types of documents can be indexed and easily retrieved using Omnidex keyword retrievals.

;Gn - Grouping

Grouping is a very powerful feature that lets you combine several MDK indexes into one logical index. This saves programming effort by requiring only one call to oaqualify to search the entire group of indexes. For example, if you grouped the keyed columns COMPANY, CONTACT and TITLE together, you could search all three of these indexes by referencing any one of them in an oaqualify procedure call.

To group different fields together, append ;Gn(where n represents a group number you assigned) to their names when specifying them at a Keyword field: prompt. For example, to group the COMPANY, CONTACT and TITLE fields from the CUSTOMERS parent table together into a group numbered “3”, you would enter:

Column name: COMPANY;G3
Column name: CONTACT;G3
Column name: TITLE;G3

You can group indexes across tables in the same domain. For example, if the CUST_NOTES child table belonged to the CUSTOMERS domain, you could group CUST_NOTES MDK index ENTERED_BY with CUSTOMERS MDK indexes COMPANY, CONTACT and TITLE:

Table will be linked in the domain of CUSTOMERS
.
.
Column name: ENTERED_BY;G3

A row will qualify if the search criteria is found in any of the columns in the group.

When grouping indexes together, the following restrictions apply:

• Any given index can only belong to one group.
• You can have up to 200 groups per environment catalog.
• You cannot group a column that you might reference as the to_column in an oajoin operation.
• You cannot group binary and ASCII (CHARACTER) columns together.
• Grouped indexes should share similar indexing options. For example, group No Parse indexes only with other No Parse indexes.
• You should not group columns that contain similar data. For example, grouping an ORDER_DATE column and a SHIP_DATE column may produce confusing results when the index is searched.

;KW - Keywording

Keyword indexing parses a text field, allowing keyword searches. This option applies to MDK indexes only.

By default, ;KW fields are

parsed, meaning each individual word is stored separately in the indexes and punctuation and white space are stripped out.
case insensitive and are translated if a translation table is specified
if an excluded words list is provided, "noise" or "stop" words are stripped out.

Example:

ID Company Name
-----------------------------------------------------

21 Dynamic Information Systems Corporation

When installed with the ;KW option, the following is stored in the indexes for this record:

21 CORPORATION
21 DYNAMIC
21 INFORMATION
21 SYSTEMS

This record can then be retrieved by including any of the 4 keywords as criteria.

;NE - No Exclude

More Info

This option is the default as of Omnidex version 4.0 but is supported for backward compatibility. It causes DBINSTAL to ignore the excluded word list for this index. It also causes zeros to be indexed for binary MDK Indexes and blanks and nulls to be indexed for ASK Indexes.

The NE option applies to MDK and ASK Indexes.

• When installed on ASCII MDK indexes, the No Exclude option (;NE) disables the excluded word list, and causes the indexing of every word in a keyed field (except for blanks, zeros or null entries).
• Normally, excluded words are not indexed, but the No Exclude option causes them to be indexed for any index on which it is installed. For example, an indexed STATE column that could contain “OR” and “ME”, the state abbreviations for Oregon and Maine, would benefit from the NE option if the excluded word list contained the words “OR” and “ME”.
• To index blanks, zeros and nulls for an ASCII MDK index, combine the No Exclude option with the No Parse option.
• When installed on binary MDK indexes, the No Exclude option causes all values to be indexed, including binary zeros. By default, binary zero values in numeric columns are excluded.
• When installed on ASK indexes, the No Exclude option causes the indexing of blanks, zeros and nulls. ASK indexes do not use the excluded word list.

;NP - No Parse

This option is the default as of Omnidex version 4.0 but is supported for backward compatibility. It causes DBINSTAL to store data contained in a column as one entire keyword, including special characters (! ? * : ; { } [ ] . ,) and blanks. This means that data in the column is not parsed into separate keywords. When using No Parse on a keyword column, the first 32 bytes of the corresponding field are indexed as one keyword, including embedded blanks, special characters, and control characters.

No Parse is useful for columns holding nine-digit zip codes containing hyphens (for example, “80033-4030”) or phone numbers containing hyphens or parentheses (for example, “(303) 444-4000”).

When searching columns that use the NP option, enclose in quotation marks argument strings that contain special characters or blanks. For example, the search string:

" ABC"*

qualifies rows with keywords that start with a blank followed by “ABC”.

The search string

" BC":" DA"

qualifies rows with keywords between those starting with one blank and the letters “BC” through those starting with a blank and the letters “DA”.

The wildcard or pattern-matching character occurs outside the quotes. If you place them within the quotes, they are included in the keyword search.

When an ASCII column is used in oajoin operations as the to_column, install the No Parse option on it. This prevents the column from being parsed, and ensures the correct correspondence between rows in the joined tables.

;NT - No Translate

More Info

This option is the default as of Omnidex version 4.0 but is supported for backward compatibility. For both MDK and ASK indexes, the No Translate (;NT) option disables:

• translation of 8-bit extended ASCII characters to their 7-bit equivalents.
• custom translations established through a translation table.
• upshifting of all lower case characters to uppercase

In a column with the ;NT option, “George Peña”, for example, would be indexed as “George” and “Peña”, not as “GEORGE” and “PENA”. Consequently, when you search for “Peña” on such a column, you must enter Peña; pena, PENA, peña, and PEÑA will not locate the row.

Use No Translate if you want to store keyword values for a particular field in case sensitive form, or if you want to prevent Omnidex from translating 8-bit extended character sets.

If you group ASK indexes installed with the No Translate option, group them with other No Translate indexes.

;RC - Record Complex

The Record Complex option can only be installed on child tables. When installed on an MDK index in a child table, it stores that index’s values as though they belong to the parent table.

By default, you can use MDK indexes installed on a child table to find and ultimately retrieve rows from either the child or its parent. When you install the Record Complex option, you restrict the use of child indexes to the qualification of parent rows. However, Record Complex indexes require less overhead to maintain. Therefore you might install the Record Complex option on any MDK indexes in a child that you would only use to find records in its parent.

For example, if you only use the STATUS column of an ORDERS child table to see which CUSTOMERS are waiting for back-ordered merchandise, you would install the Record Complex option on the STATUS index.

Applies to MDK Indexes only.

;SX - Soundex

The Soundex option, when installed on an MDK index, lets you find rows by using phonetic arguments. This feature is useful for MDK index columns containing data that may be difficult to spell (for example, Galeziowski) or that can be spelled several ways (for example, Allen). Installing Soundex on these types of column could make names easier to find.

To search using a phonetic argument, spell the argument the way it sounds and end it with an exclamation point ( ! ). For example, to find all records with the keywords "ALLEN", "ALAN" OR "ALAINE" indexed for a given key, search that key using the argument ALAN!.

The restrictions for Soundex are:

• You can install Soundex only on parsed character columns. This means that you cannot install Soundex and No Parse on the same column.
• Soundex indexes can require twice the disk space of a non-Soundex index because a keyword is indexed in both its actual and Soundex spelling. Install Soundex only where you need it.
• Soundex indexes should be grouped with other Soundex indexes. Grouping Soundex indexes with non-Soundex indexes can interfere with Soundex searches or yield inaccurate results.

;Type=t - Typecasting

Typecasting informs Omnidex when data is stored in a format that does not match the data type declared in the Omnidex environment catalog for a keyed column. To typecast a keyed column, composite index, or composite index component, append ;TYPE= t to its name, where t equals one of the following values:

D - D float values (OpenVMS only)
E - IEEE floating point values
F - F float values (OpenVMS only)
G - G float values (OpenVMS only)
I - Signed binary integer values on 2-byte boundaries
J - Signed binary integer values on 2-byte boundaries
K - Unsigned binary integer values on 2-byte boundaries
L - Unsigned 1-byte binary integer values
P - Packed decimal values
R - Floating point values (MPE/iX only)
U - Character values, all uppercase
V - C-String
X - Character values
Z - Zoned

;UK - Unique Key

The Unique Key option applies exclusively to ASK indexes.

When installed on a sorted key, it imposes a constraint on that key that prevents duplicate values from being indexed for that key. If an application tries to post a duplicate value to the ASK index, it incurs the error:

9143 - A record with this primary (unique) key value already exists.

Top