Indexes

Indexes in CachéRDD are implemented in the same way as in DBFCDX. However not all the features are available. For example, CacheRDD supports only ONE order-bag protocol. So you cannot have indexes in two order-bags. I tried my level best to find a way to overcome this limitation but I could not. The reason is that there is no separate file(s) protocol available in Cache. All index definition goes in "persistent table class" generated by the lower level engine so all indexes have "persistent" attribute by nature. You cannot empty a bag and then attach another one as can be done with DBFCDX. This also implies that you cannot change the index file (bag).

Please remember the points below in context to indexes in CacheRDD.

1.	All types of indexes ARE supported. Character, Date, Numeric, Logical.

2.	Only ONE order bag is allowed per table.

3.	Tags are NOT limited to any number.

4.	Index-key is NOT limited to any length. However limitation on micro compiled strings holds good.

5.	User defined functions are NOT supported.

6.	Harbour index-oriented functions ARE supported.

7.	Coumpound indexes ARE supported. SUBSTR( DTOS( DateBirth ), 5, 4 ) + STR( Age, 2, 0 ) Code + IIF( EMPTY( Salary ), 'YYY', 'ZZZ' )

Conditional indexes ARE supported.

FOR indexes as perpetual.
INDEX ON LName TAG 'LNAME' FOR Salary > 500 TO 'MyIndexFile'

WHILE indexes as temporary.
INDEX ON Salary TAG 'T001' WHILE Salary > 500 TO 'MyIndexFile'

WHILE indexes are always created next to all perpetual tags and remain effective for current session.

Temporary indexes ARE supported but only when created with WHILE clause.
Once created these indexes cannot be dropped. Closing the table and then re-using is the only option.
Temporary indexes does not create any table entry on the server, they are just mapped to the memory.
Two instances cannot share the temporary indexes.
Temporary indexes are populated from the client so large indexes need careful consideration.

10.	More than one field per one token of expression is NOT supported. UPPER( LName + FName ) => WRONG UPPER( LName ) + UPPER( FName ) => RIGHT For compound indexes character "+" (Plus Sign) is expression token delimiter.

11.	More than one function expression per field IS supported. SUBSTR( DTOS( DateBirth ), 5, 4 )

12.

Partial parameters passing for a function in an expression token is NOT supported.
SUBSTR( DTOS( DateBirth ), 5 ) => WRONG
SUBSTR( DTOS( DateBirth ), 5,4 ) => RIGHT
It is because in Caché field values are stored without trailing spaces
and hence at runtime SQL engine cannot assume the length of a field.
So we need to pass all arguments to a function call to simulate exact behavior.

13.

Unique indexes ARE supported but not in the strict sense like DBFCDX. Index key must ever be unique for that record to be saved. Unique indexes in SQL are constrained to have unique values (i.e., no two records can have the same collated value). So be careful while creating unique indexes with CachéRDD.

If you intend to bring into RDD the existing non-RDD created tables, be sure that those tables must have been created with SQL command 'CREATE TABLE ...' and must have the field types what Clipper supports, that is, Character, Numeric, Date and Logical. However you cannot use the existing indexes created via SQL commands. You may be required to issue INDEX ON ... command to use such indexes via CachéRDD compiled application.

Indexes are created and stored as Caché's internal file format. None of the index values are stored along with the table as a table field, be it of any type, viz., normal, compound or conditional index.

How SQL statements take advantage of RDD specific compound or conditional indexes is unknown. CachéRDD generated indexes does not confirms to SQL generated compound or conditional indexes protocol. However indexes based on a single field may be similar to CachéRDD and SQL.

Page url: http://www.vouch.info/cacherdd/index.html?cache_indexes.htm