Indexing Strategies

Domains

A domain is an Omnidex term for the way tables relate to each other in respect to the Omnidex indexes. Domains are used to join tables with a one-to-many relationship using a field common to both tables. A domain can also contain a single table.

An Omnidex domain is a logical grouping of tables that have been installed with Omnidex indexes. Queries performed against joined tables belonging to the same domain, are optimized.

Domains are named for the primary table they contain. In other words, if a domain consists of a single parent table or a parent table with one or more child tables joined to it, the domain name is the same as the parent table. If the domain contains only a single, unjoined child table, the domain name is the same as the child table.

There are four domain types: SI domain, RS domain, DR domain, and Hybrid-DR.

Omnidex domains:

• apply to MDK indexes only.
• create separate index files.
• determine what is stored in the index files.
• affect what the qualifying count represents.

Parent-only domains can be ID/SI, TR or TRR. All indexes are RC indexes.

SI (Search Item) Domain

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.

RS (Record Specific) Domain

An RS (Record Specific) Domain is a parent table prejoined to one or more child tables. This is also known as Pre-Joined indexing.

• Queries based on a parent/child relationship within the same domain are very fast in either direction (parent to child or child to parent).
• A child table may belong to one or more domains.
• The child table will be indexed as part of the parent table domain.
• A child table prejoined to a parent table belongs to the parent’s domain.
• Can query across domains using Bitmap joins and Multifinds.
• The Omnidex ID for the RS child table is a 64-bit Integer; 32-bits for the parent table's Omnidex ID and 32-bits for the child table's rowid.
• This child table cannot use Bitmap Indexing because of the 32-bit Omnidex ID requirement.

When a child table is prejoined (or linked) to a parent table, MDK indexes installed on the child table can be used to qualify and retrieve rows from either the child or its parent.

When using this type of domain, the index can be installed with either the Record Specific (default), or Record Complex option. This determines the type of information that is qualified.

When the Record Complex option is installed on an MDK index in a child table, it treats the index as if it belongs to the parent table. When rows are qualified in the child table, Omnidex returns primary keys for the parent table.

Depending on the indexing option, this domain type qualifies the following:

Child tables placed in a domain let you quickly join to the parent table or to other child tables in the same domain. For example, you could search an ACTION index to qualify ACTIVITY child rows. You could then select and fetch those rows, or you could quickly join to ACTIVITY’s Omnidex parent table, CUSTOMERS, and select and fetch the corresponding parent rows.

The index, or key column, like CUSTOMER_NO, that prejoins child rows to their unique Omnidex parent row by a common value is called a link field. This column must have been declared as a PRIMARY KEY in the parent table, and a FOREIGN KEY in any child tables that you want to prejoin to the parent. You can prejoin a child table to any parent tables that meet these criteria.

You can join a child table to several parents, creating a multi-domain child. Doing so increases the efficiency of searches across tables and domains. You can use the multi-domain child to perform multi-domain qualifications that begin in one domain but return information from a second domain.

Using our sample database ORDERS, for example, you could find all customers in Idaho that ordered items between April 1 and June 30, then use the qualified list to join to the PRODUCTS domain and return the PRODUCT NAME for each product ordered. The resulting list would contain customers in Idaho who ordered a product between April 1 and June 30 along with the name of the product they ordered.

You can still perform multi-domain qualifications without having a multi-domain child, although it may not have optimized performance.

The cost of prejoining a child table to several parents is a need to install a separate set of MDK indexes on the child for each domain where the child was linked. When inserting, deleting or updating rows of a child linked to several domains, OmniAccess must also update the MDK indexes for each domain. This can impact update performance. Also, reloading all MDK indexes for that child requires a B (Build) operation on all the domains where the child is linked.

Multi-find performance has been greatly improved and may be the preferred join method over an RS domain, depending on the database. See the Sorted Multi-Find section for information on which to choose.

Top