Home

Getting Started

Utilities

Indexing

Omnidex

Development

Tutorials

Quick Links

 

OMNIDEX

Features

Indexing Pseudocolumns

 

Features

 

PseudoColumns

Pseudo-columns allows columns to be included in the environment file that do not exist in the native database. Typically, these columns will equate to literals, constants or expressions (e.g. AMOUNT * QUANTITY). This feature is similar to the SQL Assembly Rules feature of OmniAccess; however in this case, OmniAccess calculates the value for the column, rather than the native database. Because of this, pseudo-columns are allowed on both relational and non-relational databases.

Pseudo-columns must occur after native columns in the environment file; placing them before the last native columns will produce an error when compiling the environment file. After the normal clauses for a column, such as data type and length, you may include an AS clause, which them includes syntax identical to a select-item in oaselect. Any syntax for a select-item is allowed, as long as it results in a single value and is not an aggregate. Further, any columns referenced in the AS clause must be previously declared in the same table.

Pseudo-columns are not differentiated by the Omnidex indexing kernel, and therefore may be installed with MDK and ASK indexes. Select statements that include pseudo-columns in their select-list, where clause and group by clause are fully optimized by the OmniAccess optimizer.

The following examples demonstrate the use of pseudo-columns:

column "SUBTOTAL" datatype FLOAT length 8 as "AMOUNT * QUANTITY"

column "GRANDTOTAL" datatype FLOAT length 8 as "SUBTOTAL * TAX_RATE"

column "COMPANY_SDX" datatype C STRING length 40 as "$soundex(COMPANY)"

column "COUNTYSTATE" datatype CHARACTER length 5 as "COUNTY_CODE || STATE"

 

Indexing PseudoColumns

It is important to note that pseudo-columns will increase indexing time. Indexing time is the sum of two numbers: the time it takes to index the keywords in the data and the time it takes to serially read a table.

The former is a constant and is in no way affected by the use of pseudo-columns. This is a simple formula that changes only by the total number of keyword in the data. The latter will vary by the database management system. Some DBMSs are considerably slower than others.

Pseudo-columns cause the table to be re-read multiple times, causing multiple serial reads of that table. This can increase the serial read time of this table by 50% to 100% for each pseudo-column.

 

Top