Sorting versus indexing

Sorting a table physically reorders data into a sequential order and outputs the results to a new ACL table. Indexing does not make any change to the underlying physical order of data. Instead, it creates a separate index file that references records in a table, allowing access to the records in a sequential order rather than a physical order. Data in the view is reordered in accordance with the index only while the index is active.

One process might be better suited to a particular task than the other. For example, sorting might be a better choice for investigative work because it outputs a new table that can serve as the basis for subsequent analysis. Indexing might be a better choice for informational or preliminary work because it allows you to quickly switch between different representations of the data in the active table.

There are additional benefits and drawbacks to each method. In general, it is slower and requires more disk space to sort a table than to index it, but much faster to perform subsequent analysis on a sorted table than on an indexed table. If you anticipate working with more than a small portion of the records in a table, it is better to sort the table to optimize subsequent processing speed. If disk space is limited, or you want to quickly find records with a specific value, indexing is a better choice.

Table 1 compares the benefits and drawbacks of sorting and indexing, and lists operations that require either sorting or indexing as a prerequisite.

Table 1. Sorting versus indexing

Sorting

Indexing

Outputs results to a new, physically separate ACL table

Yes

No

Physically reorders data

Yes

No

Speed of operation

Slower

Faster

Required disk space for processing

More

Less

Resulting file size

Larger

Smaller

Subsequent processing of an entire file

Faster

Slower

Searching character fields

Slower

Faster

Prerequisite for

  • Join

    (recommended, but not enforced, for the primary table)

  • Merge

  • Duplicates

  • Gaps

  • Define Relation

    (indexing of the child table key field performed automatically by ACL)

  • Join

    (applying an index to the secondary table can only be performed from the command line or in a script)

  • Merge

    (applying an index to the secondary table can only be performed from the command line or in a script)

  • Duplicates

  • Gaps

  • Find

  • Find Literal search option

  • Seek

  • Seek Expression search option

Related concepts
Testing sequential order, sorting, and indexing
About testing sequential order
About the Sort Order option and sort sequences
About sorting
About indexing
Sorting and indexing using computed fields


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