Database-level Indexing Options
 T
- TRANSLATE - to 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.
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.
 Effective
in version 4.0, No Translate and No Exclude are the default
indexing options.
Table-level Indexing Options
TR | TRR | REC
TR
- Transparency - The TR option causes Omnidex to create
a 32-bit, well-behaved ODXID for each row in the table. This
ODXID is stored in the indexes as well as in a cross reference
file that is used to link the indexes with the native index.
In order for Omnidex to use the native index as the ODXID,
it must be a 4-byte, positive integer and should also be well-behaved.
The TR option will guarantee that all these requirements are
met.
A well-behaved id is a 32-bit integer, sequentially ascending
from 1, with no gaps in the sequence. This is a vital requirement
for bitmap indexing.
If TR is not installed on a table and the native index is
a 4-byte integer, Omnidex will assume that it is also well-behaved
and proceed with the installation.
If Omnidex then encounters a native index that is not positive,
less than or equal to 0, an "ODXID not greater than 0"
error will occur, halting the installation.
If TR is not installed on a table and the native index is
NOT a 4-byte integer, TR will be invoked automatically.
TRR
- Transparency with Rowid - Omnidex requires native
indexes to uniquely identify rows. Flat-file management systems
often have no indexes associated with them. In this case,
TRR must be installed on the parent tables for MDK indexes.
Use the REC option for ASK indexes in the same situation.
The TRR option should be used when intersecting aggregation
indexes with MDK qualifications in a TR parent table.
The TRR option should also be used in tables that have composite
search items, primary keys that are a combination of multiple
columns.
Although a column may be declared as a KEY in the environment
catalog to uniquely identify rows, it is not truly a key until
it is indexed with the TRR option.
The TRR option is similar to the TR option in that it 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.
It differs, however, in that it also creates a combination
of the primary key and rowid, and uses this in the cross-reference
file in place of the non-existant native indexes.
If a table will be installed with both MDK and ASK indexes,
it is not necessary to install both the TRR and REC option
on this table. One or the other must be used, but not both.
REC
- The REC option is equivalent to the TRR option, with the
exception that it applies to tables installed with ASK indexes,
where TRR applies to tables installed with MDK indexes.
However, use of the REC option implies TRR and use of the
TRR option implies REC. This means that when one is used,
the other is not necessary, when applying to the same table.
If TRR is used on a table, it is not necessary to install
REC on the same table.
Omnidex requires native indexes to uniquely identify rows.
Flat-file management systems often have no indexes associated
with them. In this case, REC must be installed on the parent
tables for ASK indexes. Use the TRR option for MDK indexes
in the same situation.
top
Index-level Indexing Options
BI | BM | BB
| DI | EX | Gn
| 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.
top
;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, NP, and NE options.
- 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.
top
;BB - Blob Indexing
Blob indexing converts compound items to simple items for
indexing. Limited to 64K or the underlying data base row limit
(e.g. TurboImage is 4096 bytes). Will process entire column
or until null terminator is found.
- Used only for TurboImage compound data types. For other
applications, simply declare a C STRING LENGTH 65536.
- Used only for MDK Indexing.
top
;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
an MDK Index with ;NP can be only 32 bytes long.
- Distributed Indexes are maintained on-line for DR and
ID-SI parents, but they are not maintained on-line for transparency
parents.
- 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 @@4 (where n is 4), then only two passes
through the extract file are needed.
top
;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.
top
;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 nrepresents
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.
top
;NE - No Exclude
This
option 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.
top
;NP - No Parse
No Parse tells 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.
top
;NT - No Translate
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.
top
;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.
top
;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.
top
;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:
top
;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
|
Database-level