You can test the indexes that you create using the Table Browser. As you learned earlier in this chapter, the primary features provided by indexes are customized views and high-speed searches (FTS indexes only provide high-speed searches). The Table Browser permits you to test both of these aspects of your indexes.
Use the following steps to test the indexes that you created in the preceding section:
With the CUST.ADT table displayed in the Table Browser, note the natural order of the records.
Using the Order By dropdown list that appears in the lower-left corner of the Table Browser, select the CUSTOMER ID index. Note that the records are displayed sorted by Customer ID, as shown in Figure 3-4, confirming that this index is providing the proper order view.
Figure 3-4: Setting the index to CUSTOMER ID sorts the customer records by the Customer ID field.
To test the speed aspect of the index, enter Customer ID = 15198 in the field below the Set Filter button (shown in Figure 3-4). Now click Set Filter. Two things should happen. First, only the record for the customer whose Customer ID field contains the value 15198 will be displayed, as shown in Figure 3-5. Second, a green circle should appear to the right of the Set Filter button. (The button’s caption changes to “Clear Filter.”) This green circle indicates that the filter used an AOF (Advantage Optimized Filter). AOFs are one of the important performance-related objects that ADS can create from indexes. Click Clear Filter to remove the filter.
Figure 3-5: An optimized filter displays a subset of records in the table.
Now enter Active = False in the field below the Set Filter button, and then click Set Filter. Although a filter is applied, displaying only customers whose accounts are no longer active, the circle to the right of the Set Filter button is red. This red circle indicates that no indexes were used to build this filter. In other words, none of the table’s indexes contributed to the location of the matching record. As a result, this operation was much slower than the filter applied in the preceding step. If there were many more records in this table, the difference would have been noticeable. Click Clear Filter to remove the filter.
Now set the Order By dropdown list to By Customer. Notice that a case- insensitive ordering of the customer records appears, first by last name and then by first name.
Next set Order By to Full Name. Notice that this also provides a sorting by customer, but this one is sorted by first name and then by last name. What is not obvious about this view is that this is a case-sensitive sort. You can verify this by changing the first name of customer 4 from “Jamie” to “jamie.” If you click the Post edit button in the navigator after making this change, you will see this record reordered to the last position in the table. This is because case-sensitive sorts place uppercase letters before lowercase letters. Change customer 4’s first name back to “Jamie” and click Post edit again.
Finally, let’s test the Active Customers index. Set Order By to Active Customers. As shown in Figure 3-6, the table now appears only to contain customers where the Active field contains the value True. Also note that the records in the displayed view are sorted by Date Account Opened, in descending order.
Figure 3-6: The Active Customers conditional index shows only active customers, sorting these records by Date Account Opened and in descending order.
If you double-click on the field below the Set Filter button, the Filter dialog box is displayed. This dialog box provides a larger field for entering filter expressions, making the testing of filter expressions more convenient.