Further db4 Index Question 2

[maddytk]

So a db4 Index is used for sorting the records in a Table. Can an Index exclude records from a Table? If so, where is the Condition? My Table has many records, but the Index only contains 4. Another Index on the same table contains 4 different records. thanks.

 

[tviman]

Unfortunately, indexes are applied against the entire table. You can use an SQL query to exclude records that don't meet a certain criteria, something like this:

select a, b, c
from table
where a <> something
order by b

There's always a better way...

 

[1oldfoxman]

maddytk:

It's possible to create in index on a datafile using a filter condition so that when the index is activated only some of the records display or are included in any processing of that table. This may have been done as follows in dbase:

INDEX ON MyField TO MyIndex FOR Mycondition

For example:
USE CUSTOMERS
INDEX ON CUSTOMER_NAME TO TAG CNAME OF CUSTOMERS;
FOR ZIP_CODE = '04843'

Another way of getting limited records included in an index is with UNIQUE indexes, for example the following will yield only one record for each unique zip code in the Cutomers table:

USE CUSTOMERS
INDEX ON ZIP_CODE TO TAG ZIPCODE OF CUSTOMERS UNIQUE

Hope this helps.
Dennis

 

[1oldfoxman]

Oops, my examples included some syntax from FoxPro functionality (for compound indexes.... habit). They should have been, for dBaseIV:

For example:
USE CUSTOMERS
INDEX ON CUSTOMER_NAME TO CNAME ;
FOR ZIP_CODE = '04843'

Another way of getting limited records included in an index is with UNIQUE indexes, for example the following will yield only one record for each unique zip code in the Cutomers table:

USE CUSTOMERS
INDEX ON ZIP_CODE TO ZIPCODE UNIQUE


Also some further info I forgot to mention - the UNIQUE clause creates an index that contains the first record encountered only for each unique key value.

Dennis

 

[tviman]

The examples by 1oldfoxman will yield a filtered index but this type of index cannot be used in conjunction with a table to update the table AND the index. This type of indexing scheme is better suited to a &quot;SORT&quot; command - and is actually faster than the index he mentions.

The only reason I mention this is because any table that has an index should never be updated without the index(s) being open at the same time. This way, the index file(s) are updated at the same time the table is updated. There's always a better way...

 

[1oldfoxman]

Correctamundo..... I was only trying to explain how indexes can end up with less than all the records in a table, which sounds like what Maddytk is seeing.