Index, Reindex, DELETE TAG, SET UNIQUE, Set(“Unique”)

These commands all relate to the care and feeding of indexes. INDEX lets you create regular, unique and candidate indexes. REINDEX re-creates existing indexes. DELETE TAG lets you remove index tags from a compound index file. SET UNIQUE determines whether newly created indexes are regular or unique—leave it set to OFF all the time.

Usage

INDEX ON eKeyExpr
        TO IDXFile | TAG TagName [ OF CDXFile ]
        [ FOR lFilter ]
        [ COMPACT ]
        [ ASCENDING | DESCENDING ]
        [ UNIQUE | CANDIDATE ]
        [ ADDITIVE ]

FoxPro’s indexes can be divided up several ways. The first distinction is in how and where they’re stored. There are two kinds of index files: stand-alone and compound. As the names suggest, a stand-alone index holds data for a single key, while a compound index can hold multiple keys.

By default, stand-alone indexes are stored with an extension of IDX. These are compatible with all versions of FoxPro going back to FoxBase. Stand-alone indexes can also have the COMPACT attribute, which reduces their storage requirements. Since compact stand-alone indexes were introduced at the same time as compound indexes, there’s not much reason to use them. In fact, the only reason to use stand-alone indexes at all is to maintain compatibility with other products or older versions that can’t read compound index files.

Compound indexes use a default extension of CDX. Each index in the file is called a tag and has a name that identifies it. Within a single CDX, each tag’s name must be unique, but a table can have several tags of the same name in different compound index files.

Every table may have a structural CDX file. That’s a compound index file that has the same name as the table—only the extension is different. If a table has a structural CDX, it’s automatically opened whenever the table is opened, and closed whenever the table is closed. We strongly suggest you use the structural CDX for every index you plan to maintain. Use other compound index files or stand-alone indexes only for infrequent, on-the-fly operations. That way, you’re sure that your indexes are always kept up-to-date.

The next question is what goes into the index. There are several choices here.

A regular index contains a key for each record in the table.

A candidate index contains a key for each record in the table and prohibits duplicates in the key expression—attempting to add a record with an existing key generates an error. Candidate indexes can also be created with CREATE TABLE and ALTER TABLE. Those two commands also let you create a primary index—that’s a candidate index that you’ve designed as the primary key for the table. Only tables in a database can have primary indexes. You can’t create primary indexes with INDEX, only by using CREATE TABLE, ALTER TABLE, or the Table Designer.

“Unique” indexes are misnamed. (In fact, candidate indexes are unique. These are not.) Back in the FoxBase and FoxPro 1.0 days, they were useful for temporarily creating a list of distinct values in a table. However, since SQL SELECT was added in FoxPro 2.0 (yes, more than a decade ago), we don’t think there’s any reason to use them anymore. A so-called unique index contains a key only for the first record that has a particular key value. That is, once a key value occurs, no other records with that key value get added to the index. There’s no mechanism to enforce uniqueness here, just a way to find one of each. However, unique indexes are not properly maintained. If you delete a record that’s represented in the index, FoxPro does not add the next record in the table that has the same key value. Don’t ever use “unique” indexes—there’s always a better way to do it.

Each of the index types can be filtered by adding the FOR clause. In that case, only the records meeting lFilter are added to the index. Filtered indexes are properly maintained—as records change, the index is updated to include those that now qualify. However, any index that’s filtered in this way is ignored by Rushmore when it attempts to optimize commands, so use these cautiously. (Don’t be misled by the VFP Help, which seems to imply that filtered tags are used by Rushmore. In fact, what it’s really saying is that Rushmore will optimize the INDEX command itself if appropriate tags exist.)

In VFP 3 and VFP 5, the only way to filter a primary key is through the Table Designer. The FOR clause in ALTER TABLE (added in VFP 6) lets you create filtered indexes (primary, unique, or foreign key) directly.

VFP 7 Service Pack 1 fixed an ugly bug that was introduced in VFP 7: None of the records added to the table after a filtered index was created were visible until you reindexed.

Indexes can be created in either ascending or descending order. Regardless of how you create them, you can use them in either order as well by specifying ASCENDING or DESCENDING in the SET ORDER command. The only advantage of creating an index in descending order is that the first time you SET ORDER TO it, if you don’t specify, it’s descending. We don’t think that’s enough of an advantage to make it worth worrying about whether an index is ascending or descending. Create all indexes in ascending order and use them as needed. As we mention in SET ORDER TO, you need to use the ASCENDING/DESCENDING keyword only the first time you open an index in a session. Once you do so, it’s opened in that orientation until it’s explicitly set the other way. In fact, if a table is open more than once using the same tag for ordering, all the copies are either ascending or descending. You can’t SET ORDER TO ATag ASCENDING in one work area and SET ORDER TO ATag DESCENDING in another at the same time (well, you can, but then they both end up in descending order).

An index key can be simply a single field, or it can be any valid FoxPro expression (though it normally contains a field of the table). For character fields, it’s almost always a good idea to use UPPER() in the index key so you can search without worrying about case. For example, the Company Name tag for TasTrade’s Customer table is UPPER(COMPANY_NAME). However, remember that for Rushmore optimization, you then have to use UPPER() in the expressions in SEEK, SET FILTER TO, SQL SELECT, and other commands. See “Faster Than a Speeding Bullet” in the front section of the book for information on how Rushmore works.

When indexing on fields of different types, the best bet is generally to convert them all to strings and concatenate the strings. For example, a key involving a string and a date normally uses DTOS() to put the date in character format and in true date order (including the year).

Occasionally, you need an index that uses two fields in different orders. For example, you might want to index an Orders table in customer-number order with each customer’s orders in reverse date order. To do this sort of thing requires a trick—subtract the field you want in descending order from a suitably large number or date. Date math lets you use the trick with both numbers and dates. There’s an example below.

VFP lets you specify an index key that doesn’t have a fixed length (like ALLTRIM(cLastName)). However, internally, it pads the index back to a standard length. Most often, we’ve seen people do this when combining fields (as in ALLTRIM(cLastName)+ALLTRIM(cFirstName)). This is a bad idea and can actually put the records in the wrong order. Well, “wrong” as in you didn’t mean that one; VFP is just doing what you told it. Since index keys always have fixed length anyway, there’s no reason to trim them.

FoxPro lets you create indexes whose keys reference another table. Don’t! Just like mama told you, just because you can doesn’t always mean you should. Like “unique” indexes, indexes with foreign fields are not automatically maintained. There’s always another way to do it either by setting a relation or using SELECT-SQL. In the same vein, user-defined functions can be used as part of an index expression. Don’t do it—performance can be slowed by several orders of magnitude; and loss of, or change to, the UDF code can wreak havoc with your data design.

Finally, the ADDITIVE clause. This is one we haven’t used for years. When you’re working with non-structural indexes, opening one index closes others. Creating an index is the same as opening one from this point of view. ADDITIVE lets you create a new stand-alone or non-structural compound index without closing other stand-alone or non-structural compound indexes.

You should have exclusive use of a table before you start creating indexes. A stand-alone index or the first tag of a compound structural index can be created with only shared use of a table, but additional tags cannot be added to the structural index. We think that if you are doing system-wide maintenance like indexing, it is always a good idea to open the table exclusively.

You can create indexes for cursors, whether they were created with CREATE CURSOR or SQL SELECT, or by opening a view. When you close the cursor, VFP automatically deletes any index files for the cursor. However, you have to follow a couple of rules. First, attempting to create indexes while table buffering is in effect causes an error: “Command cannot be issued on a table with cursors in table buffering mode.” So, if you want to use table buffering, you have to temporarily change to row buffering, create the indexes, and then switch back to table buffering. The second example below shows this.

The second rule is that if the cursor is read-only, you can create only a single tag of a structural CDX for the cursor; you’ll get an error if you try to create a second one. You can create as many non-structural indexes (IDX or CDX) as you want. If you need more than one tag in a structural CDX, use a read-write cursor instead. In versions prior to VFP 7, the way to do this for a cursor created by SQL SELECT is to open a second instance of the cursor using code similar to the following:

SELECT * FROM Customers INTO CURSOR CustomerCursor
SELECT 0
USE DBF('CustomerCursor') AGAIN
INDEX ON ...
INDEX ON ...

Starting in VFP 7, you can use the READWRITE clause of a SQL SELECT statement to create a read-write cursor.

In the first release of VFP 7, you couldn't create an index on a cursor created by opening a view or an IDX-style index for a table belonging to a database if the DBC was opened with the NOUPDATE clause or was marked as read-only at the operating system level. In previous versions, you could. Since in neither case is the index information written to the DBC, this didn't make sense. Fortunately, this bug was fixed in Service Pack 1.

VFP 7 Service Pack 1 fixes another, more obscure bug, albeit one that goes back to at least VFP 5: Under certain settings of foreground memory and with table buffering turned on, making certain changes to a lot of key fields could cause VFP to hang.

In VFP 7, if the table belongs to a database that has Database Events turned on, the BeforeModifyTable and AfterModifyTable events fire when you use the INDEX command.

Example

USE Orders
INDEX ON Order_Id TAG Order_Id CANDIDATE && Prevent duplicates.
INDEX ON UPPER(Ship_To_Country) TAG ShipCtry
* This one's in customer order, then reverse date order.
INDEX ON UPPER(Customer_Id)+STR({12/31/2099}-Order_Date) ;
         TAG CustDate
* Create an index in date order, only for records with
* some notes.
INDEX ON Order_Date FOR NOT EMPTY(Notes)
USE MyView
* Turn on optimistic table buffering.
CURSORSETPROP('Buffering', 5)
* Do some other things here
* Now we want to create an index, so switch to row buffering
* temporarily.
CURSORSETPROP('Buffering', 3)
INDEX ON SomeField TAG SomeTagName
CURSORSETPROP('Buffering', 5)

Usage

REINDEX [ COMPACT ]

This command regenerates all open indexes in the current work area. It sounds like a really useful command and we’ve been known to use it occasionally. But it has no place in an application. If the index header information is damaged, REINDEX leaves the damage there—it doesn’t fix it. Also, it causes “CDX bloat”, because it doesn’t overwrite the space used in the CDX file by the former indexes. Instead, it marks those indexes as not being used anymore and adds the new ones to the end of the file. We’ve seen CDX grow to many times the size of the DBF file simply by repeatedly using REINDEX. For these reasons, it’s much better to delete all the tags and re-create them from scratch. (But see our comments below.)

The COMPACT keyword converts stand-alone indexes to compact, stand-alone indexes. We’ve never used that one.

Usage

DELETE TAG TagName1 [ OF CDXFile1 ]
        [ , TagName2 [ OF CDXFile2 ] [ , ... ] ]
DELETE TAG ALL [ OF CDXFile3 ]

As the name suggests, DELETE TAG removes a single index from a compound index file. When specifying individual tags, you need the OF clause only if there are multiple tags with the same name.

Since the introduction of compound index files, DELETE TAG ALL has been a key component of just about every file maintenance system. Dozens of books and articles tell you to DELETE TAG ALL followed by INDEX to rebuild your indexes. In fact, we’ve told people to do that many times. In addition to avoiding the problems of REINDEX, it does a nice job of cleaning out the CDX file, throwing away any residual garbage that might be lurking there. That’s because it makes the CDX file disappear. Deleting all the tags in a CDX, either one by one or with DELETE TAG ALL, actually deletes the file.

Unfortunately, for tables in a database, just deleting tags creates all kinds of trouble. For one thing, not only are the indexes removed from the table, they’re also removed from the DBC. However, a bigger problem is that deleting the tags for a table also deletes any persistent relationships for that table. That makes sense, since persistent relationships are based on indexes, but re-creating those persistent relationships is kind of a hassle; you have to use ALTER TABLE on the child table in the relationship, even if the index you deleted belonged to the parent table. Like PACK, this is another case where a good database maintenance tool like Stonefield Database Toolkit is worth its weight in gold.

In VFP 7, if the table belongs to a database that has Database Events turned on, the BeforeModifyTable and AfterModifyTable events fire when you remove tags using this command.

Example

* To clean up a free table's indexes, you can do something like:
USE MyTable
DELETE TAG ALL
INDEX ON UPPER(cLastName+cFirstName) TAG Name
INDEX ON DateField TAG DateField

Usage

SET UNIQUE ON | OFF
cUniqueSetting = SET("UNIQUE")

This setting determines whether indexes are “unique” by default. Don’t use it. In fact, we suggest you don’t use so-called unique indexes at all. But, if you insist on doing so, use the UNIQUE keyword of INDEX. Don’t mess with this system-wide (actually, now it’s data session-wide) setting. The corresponding and undocumented SET(“UNIQUE”) tells you the current setting.

See Also

AfterModifyTable, Alter Table, ATagInfo(), BeforeModifyTable, Create Cursor, Create Table, CursorSetProp(), Descending(), For(), Key(), Open Database, Seek, Select-SQL, Set Order, Tag(), TagCount(), TagNo(), Unique()