Indexing Strategies

Omnidex indexes can be delineated into two major sections. Multi-Dimensional Keyword (MDK) Indexes and Aggregation/Sorted Key (ASK) Indexes.

• MDK Indexes provide a very powerful and flexible means of accessing data. Individual data values in a column are indexed as keywords, allowing data to be qualified for retrieval based on keyword values or partial keyword values, without knowing the primary key or rowid. They also support progressive, or “drill-down”, searches.
• Aggregation/Sorted Key (ASK) Indexes provide sorted retrieval capabilities as well as aggregation index retrieval speeds unparalleled by normal dbms aggregations. Data is retrieved in sorted order based on key values without knowing the native key or rowid. ASK indexes are used for sorted retrievals, range retrievals, high-speed aggregations, and comparison operations.

Composite indexes use part of a column or a combination or columns in an index. They can be created from both MDK and ASK indexes to give some additional flexibility when performing searches.

There are two special ASK composite indexes:

Index-only access composite indexes allow high speed searches and data warehousing installations.
Aggregate indexes quickly display sums, averages, counts, and minimum or maximum values for a group of rows.
An installation can include any or all index types. The types of indexes installed depends on the the questions most commonly asked.

Notes

• In DBINSTAL, when using the SEQROW Build option, retrievals will be restricted to only aggregations; detail data is not available.
• The SEQROW option will be made obsolete in a future version of Omnidex.
• Re-build the indexes using DBINSTAL when online updates increase the size of the database by 20% or more. This process repacks the indexes and optimizes retrieval performance.
• If using Zoned fields which contain dates and other character oriented data, use DBINSTAL to typecast the field to character (type=X) so that the Omnidex ODBC driver can treat the field as a text field.
• If using a redefinable row ID in a table, create a native index on the redefinable row ID to simplify the indexing process and facilitate more efficient retrieval.
• Use a sequentially ascending 4-byte integer (in the Omnidex Environment, Integer Length 4) for the Omnidex ID and the table primary key for maximum indexing and retrieval performance. Omnidex uses this key as the row ID.
• Use the Bitmap (;BM) indexing option when the cardinality is less than 30.
• Use the Bitmap (;BM) indexing option only when the indexes can be maintained as a batch process.
• Avoid using ANSI SQL2 reserved words for column or table names, or for Omnidex composite index names.
• If SQL reserved words are used in column or table names, the names must appear in quotation marks in calls to the OmniAccess API.
• Use different Environment logical names for each Environment Catalog if multiple Environment Catalogs will be accessed by the same program.

Indexing Parent Tables

Parent tables typically contain rows that are each uniquely identifiable by the value in one column (the primary key). There is a one-to-one relationship between each unique identifying value in this column and the row that contains that value.

For example, in a CUSTOMERS table, each row is identified by a unique number in CUSTOMER_NO. CUSTOMERS is considered a parent table, and CUSTOMER_NO is its primary key.

The primary key for each parent table is defined in the environment catalog as a KEY declaration in the TABLE definition.

Installing an MDK (Multi-Dimensional Keyword) index on a single parent table creates an SI domain. A parent table cannot be in more than one domain.

An SI domain is named after the parent table that it contains. For example, if you installed an index on the parent table CUSTOMERS, the name of the domain would be CUSTOMERS. MDK indexes on parent tables qualify parent rows.

Always install MDK indexes on parent tables first. Even if you don’t install indexes on the parent table, you must specify its name at a Table Name? prompt during MDK index installation if you want to prejoin child tables to it to create a domain.

Indexing Child Tables

Unlike a parent table, child table rows may share the same value for any given column. A child table does not usually have a unique key field, meaning that a value in any given field can occur in any or all rows in the child table.

A child table has a field that "links" it to the key field in a parent table and usually has a one-to-many relationship with the parent table.

For example, the ORDERS child table contains order entry information for customers defined in the CUSTOMERS parent table. The tables are linked by the common field, CUSTOMER_NO.

However, while the CUSTOMERS parent table can have only one row containing a specific customer number, the ORDERS child table may contain several rows with that customer number or several orders for that customer.

A child table can be indexed:

• as a DR (detail record) domain (preferred). This indexes the child table as a stand-alone table, similar to a parent table's SI domain, without prejoining the child table to the parent table. The child and parent tables must be manually joined for qualifies and selects.
• in an SI (Search Item) with RS (Record Specific)(default) domain. This prejoins the child table to the parent table. Qualifies rows in the child table with the ability to access parent rows based on the child table qualification.
• in an SI (Search Item) with RC (Record Complex) domain. This prejoins the child table to the parent table. Returns only parent rows, no matter where the qualification was performed, child table or parent table.
• as a Hybrid DR domain.
• as a DR domain
• in an RS domain
• as a Hybrid DR (a variation of a multi-domain child)

ID List Indexing

ID List Indexing is the default and is better for performance if more than 30 unique values are in the data base. Id List Indexing is more space efficient if more than 20 unique values are in the data base. Id List Indexing can also be more efficient on less than 30 unique values if only a percentage of the rows contain a value.

Top