Aggregation/Sorted Key (ASK) Indexes

Aggregation/Sorted Key (ASK) Indexes provide high-speed aggregation and sorted key retrieval capabilities.

Data returned by an ASK index qualification is returned in order, sorted numerically or alphabetically by key value.

Aggregations performed on an Aggregation index are calculated lightning fast and returned grouped and sorted in the order specified when the indexes were installed.

ASK indexes allow qualifications on rows whose indexed columns begin with a certain string (ASCII only) or that fall within a range of values, and return the rows sorted by the values in the indexed column.

See Composite Indexes for more information and rules about creating composite MDK indexes.

Advantages

Although many DBMSs provide a means of sorting rows, Omnidex ASK indexes offer several advantages over them.

• ASK indexes do not store entire rows redundantly. They store only the indexed column values and the row IDs.
• ASK indexes can be maintained automatically, in real time, as rows are added to a table.
• ASK indexes let you search using ranges, relational operations (<, <=, >, >=), and wildcard criteria.
• ASK composite indexes provide an efficient means to sorting rows by several columns.
• If all of the requested data is stored in the indexes, for example aggregations on the data without detail data, the database is never touched. The aggregations are performed against the indexes eliminating the overhead incurred when connecting to and requesting rows from the dbms.
• The need for summary tables is completely eliminated saving disk space used by redundant data.

When to Use ASK Indexes

ASK indexes should be used when criteria will consist of a range in the data, such as between two dates, or a relational operation, such as amount>=100.

ASK indexes should be used to retrieve rows in sorted order, including rows qualified by an MDK index. When the underlying database does not provide a native sorting method, a flat file database for instance, ASK indexes are the only way to retrieve rows in sorted order.

Use ASK indexes when performing aggregations, SUM, MIN, MAX, AVG, COUNT. Also use ASK indexes to define grouping for one or more columns when performing aggregations.

Sorting

Install ASK indexes on columns that will be used for sorting, such as a name field or a ZIP_CODE field for mass mailings. ASK indexes can be installed on an MDK indexed column to return data qualified by a keyword search, in sorted order.

Create composite sorted indexes that sort rows based on multiple columns.

Table: PRODUCTS
Key field: PRODUCT_NO
Key field: PRODUCT_NAME
Key field: CLASSNAME_AG!
Component 1 (Field [,start-byte,lgth]) PRODUCT_CLASS
Component 2 (Field [,start-byte,lgth]) PRODUCT_NAME
Component 3 (Field [,start-byte,lgth]) SALES_PRICE
Component 4 (Field [,start-byte,lgth]) PRODUCT_COST
Component 5 (Field [,start-byte,lgth]) /

Omnidex ASK indexes reduce the data redundancy usually associated with sorting rows. There is no need to maintain external files of sorted rows, or to maintain pointers for sort paths. This makes Omnidex ASK indexes faster and more efficient to use than other means of sorting rows.

Use ASK Indexes to retrieve records in sorted order from a flat file database or dbms that does not have native sorted indexing capabilities.

Aggregations and Grouping

An Aggregation Index is a composite ASK index used for calculating, grouping, and sorting aggregations quickly.

Aggregations are used to summarize the collective values of qualified rows for a specified column, using SUM, MIN, MAX, AVG, and COUNT. For example, AVG(SALES_AMT) is an aggregation that would return the average value of the SALES_AMT column for a group of selected rows.

The DBINSTAL example above creates an ASK index on the PRODUCTS table with a composite aggregation index on the PRODUCT_CLASS, PRODUCT_NAME, SALES_PRICE and PRODUCT_COST fields. This allows aggregations to be performed on the SALES_PRICE and PRODUCT_COST fields grouped by PRODUCT_CLASS and PRODUCT_NAME, and sorting done on the PRODUCT_NO and PRODUCT_NAME fields.

The following SQL statement will be optimized using this index:

SELECT
PRODUCT_NAME,
SUM(SALES_PRICE),
SUM(PRODUCT_COST)
FROM PRODUCTS
GROUP BY PRODUCT_CLASS, PRODUCT_NAME
ORDER BY 1,2

Sorted Searches

ASK indexes support the following search operations:

• range retrievals
• relational retrievals (like >, <, >= and<=)
• partial-key searches

ASK indexes also support partial-key and wildcard searches. This is especially useful when searching on hierarchical code fields. Account numbers are often hierarchical fields where the first few bytes represent a regional code, the next few bytes a SIC code, and so on. When an ASK index is installed on a hierarchical code field, it lets you find rows as generally or as specifically as you want, depending on how much of the key value you specify in your search argument.

For example, if you used a partial criterion, like 80*, against a ZIP_CODE ASK index, you would find rows where the first two bytes of the zip codes match the two-byte argument.

A report of the qualifying rows might look like this:

Top