Friday, June 11, 2010

ADS 10 Tip #10 – Binary Indexes

A binary index is a very compact index which contains a bitmask which can be used to optimize some operations. There are significant performance gains when creating a binary index on deleted records, using the DELETED() expression. This index can be used by the Advantage Optimized Filter (AOF) engine to improve SQL performance when there are many deleted records in a table.

DeletedIndex You create a binary index by specifying the ADS_BINARY_INDEX (4096) option when using sp_CreateIndex or AdsCreateIndex90. You can also create a binary index using the Table Designer in Advantage Data Architect. Clicking on the Add DELETED() Index on the Additional Index Definitions tab will add the index to the table. DELETED() indexes can be created on ADTs and DBF table types.

Binary indexes can also be created on any logical field when using DBF files. This can improve SQL operations when the value of a logical field is used in the WHERE clause. Binary indexes are most efficient when the balance between true and false values is nearly equal. If the values are unbalanced a traditional index may be more efficient.

Binary indexes have a few limitations.

  • The result of the index expression must be logical
  • The result of the index expression cannot be NULL
  • The index cannot be conditional (it cannot have a FOR clause)
  • The index cannot be descending or unique

Because binary indexes cannot be built upon an expression that will evaluate to NULL you cannot create a binary index on a logical field in an ADT file.

No comments: