About indexing

Indexing creates a separate index file (.inx file) that allows access to the records in an ACL table in a sequential order rather than a physical order (that is, the raw data order). Indexing does not physically reorder data in tables. However, when a table’s index is active, the data in the view is reordered in accordance with the order specified by the index, and analytical operations process the data based on this order. When the index is inactive, the records in a view revert to their original physical order. You can index any type of field, including computed fields and ad hoc expressions, regardless of data type. Indexing on logical fields requires that Include Filters in Field Lists is selected ( Tools > Options > Interface).

When an index is active, the word Indexed follows the table record count in the status bar. For example: 500 Records Indexed. Upon opening an ACL table, any existing indexes are inactive by default.

You can create multiple indexes for a single table, and switch between indexes as required, which can be useful when initially assessing a set of data. Only one index can be active at a time. If a table has more than one view, all views are subject to an active index.

You can index records using one key field, or you can create nested indexing schemes by indexing on multiple key fields (primary key field, secondary key field, and so on). Nested indexing supports mixing ascending and descending order, and mixing data types, across key fields. For example, a transaction table could be indexed in ascending order on a key date field, and within each day in descending order on a key amount field.

Table 1. Nested indexing with mixed ascending and descending order

Date field

(ascending)

Amount field

(descending, nested)

15 Jan 2011

$2300.00

15 Jan 2011

$1200.00

15 Jan 2011

 $600.00

16 Jan 2011

 $900.00

16 Jan 2011

 $100.00

17 Jan 2011

$4700.00

17 Jan 2011

 $900.00

17 Jan 2011

 $500.00

Indexing is restricted to ACL tables – that is, tables with a .fil source data file. You can index both local and server-based ACL tables if they have .fil files. You cannot index database tables that you connect to using a database profile, because there is no .fil file. Data is read directly from the database. To order data in this situation, you can use a SQL ORDER clause in the Data Definition Wizard while accessing the database.

Indexing is a prerequisite for using the Find Literal and Seek Expression options in the Search dialog box when searching ACL tables. (The options are the equivalent of the FIND and SEEK commands.) These options are available only if a table is indexed and the index is active, and they work only if the index’s primary key field is a character field indexed in ascending order. The table can have a nested index, but only the primary key field is searched.

Sorting records is an alternative to indexing them, and in some situations may be a better choice. For more information, see Sorting versus indexing.

Conditional indexes

Indexes can include If, First, Next, and While parameters, in which case they become conditional indexes. Only those records that match the condition are indexed, or are displayed or available for analysis when the conditional index is active. Every time you activate the index the condition is automatically reapplied. You can facilitate certain types of analysis by using conditional indexes to create subsets of larger tables.

When a conditional index with an If parameter is active, the words Filtered Index follow the table record count in the status bar. For example: 500 Records Filtered Index. When conditional indexes with First, Next, and While parameters are active, the word Indexed follows the table record count, like indexes without any conditions.

When creating a conditional index with an If parameter or filter, you can include a global filter (filter on a view), a local filter (filter within a command), or both. Table 2 provides examples of including filters in indexes, and shows the effect that the indexes have on the sample data. The filters are part of the indexing syntax, which you can view in the log, or in the index details. For more information, see Viewing index details.

Table 2. Global and local filters in indexes

Type of filter

Description/Indexing syntax

vendor_ID

trans_amount

None

No index

(physical order)

212

108

359

108

359

212

359

212

359

108

1400.00

3400.00

1600.00

1100.00

3400.00

1200.00

2200.00

1700.00

1400.00

2300.00

Global

Index contains only vendor #359 records

INDEX ON trans_amount TO “vendor 359 transactions”

Global filter: vendor_ID = “359”

359

359

359

359

1400.00

1600.00

2200.00

3400.00

Local

Index contains only transaction amounts $2000 or greater

INDEX ON trans_amount IF trans_amount >= 2000 TO “trans amount 2000 or greater”

359

108

108

359

2200.00

2300.00

3400.00

3400.00

Global-Local

Index contains only vendor #359 records with transaction amounts of $2000 or greater

INDEX ON trans_amount IF trans_amount >= 2000 TO “vendor 359 transactions 2000 or greater”

Global filter: vendor_ID = “359”

359

359

2200.00

3400.00

Related concepts
Testing sequential order, sorting, and indexing
About testing sequential order
About the Sort Order option and sort sequences
Sorting versus indexing
About sorting
Sorting and indexing using computed fields
About key fields
Related tasks
Indexing records
Activating and deactivating indexes
Viewing index details
Maintaining indexes
Sorting or indexing records using computed fields


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback