TITLE:Back-to-Basics: create-index

ISSUE:Multi-value Solutions Jan '98

AUTHOR:Nathan Rector

COMPANY:Natec Systems

EMAIL:nater@northcoast.com

HTTP:www.northcoast.com/~nater/

With the release of Advanced Pick, Pick Systems started including an system level B-Tree indexing for users to make use of. Prior to Advanced Pick, the only B-tree available for the users to use were third-party programs which mainly consisted of basic programs to do the work of the B-Tree functions.

With the introduction of the System level B-trees, most of the complex work that originally had to be written directly into programs did not have to exist anymore. The operating system did work of adding, deleting and updating of the B-tree of the users. There was no longer any problems with records getting updated or deleted and the keys in the B-tree not being updated.

The Advanced Pick B-tree is very open and powerful, but does have limitations. To create an index with the CREATE-INDEX verb, all a user needs is an understanding of A-correlatives.

CREATE-INDEX CUSTOMER A1

This creates an index for all the items in attribute 1.

CREATE-INDEX INVOICE A1(TCUSTOMER;X;;1)

This creates an index in the invoice file for all the values found in the customer file attribute 1.

CREATE-INDEX CUSTOMER A1:2

This creates an index in the customer file with attribute 1 and attribute 2 concatenated together.

The limitation that exists with Advanced Pick B-trees is that you can not use the 'n(attribute name)' A-correlative type.

Advanced Pick B-trees are left justified index only. This causes problems if you are indexing dates. If you are looking at a date from left to right, then an internal date of 9867 is greater than 10234 since 9 is larger than 1. In order to allow numbers to be indexed they have to masked with zero's to make the number of characters of each value the same length.

CREATE-INDEX INVOICE A4(MR(%6))

Another limitation that exists with Advanced Pick B-trees is if you want to use the B-trees from ACCESS. If you have simple index key of 'A1' then an ACCESS statement using the dictionary name of the attribute would use the B-tree, but if you create a more complex key, such as 'A1:2', ACCESS will ignore the key.

Another limitation exists when you use more than one WITH statement in the ACCESS statement. Even if one of the WITH statements is a dictionary item that's index, the B-tree will be ignored when doing the select.

If you wish to select information from a complex key at TCL you will have to create a program to do so.

There are a two options that can be useful when working with CREATE-INDEX:

o - Overwrites existing indices

s - suppress the display of the running count as the index is created.

If you use an '*' instead of specifying an A-correlative, all the indices on the file will be processed.

TITLE:Back-to-Basics: create-index

ISSUE:Multi-value Solutions Jan '98

AUTHOR:Nathan Rector

COMPANY:Natec Systems

EMAIL:nater@northcoast.com

HTTP:www.northcoast.com/~nater/

With the release of Advanced Pick, Pick Systems started including an system level B-Tree indexing for users to make use of. Prior to Advanced Pick, the only B-tree available for the users to use were third-party programs which mainly consisted of basic programs to do the work of the B-Tree functions.

With the introduction of the System level B-trees, most of the complex work that originally had to be written directly into programs did not have to exist anymore. The operating system did work of adding, deleting and updating of the B-tree of the users. There was no longer any problems with records getting updated or deleted and the keys in the B-tree not being updated.

The Advanced Pick B-tree is very open and powerful, but does have limitations. To create an index with the CREATE-INDEX verb, all a user needs is an understanding of A-correlatives.

CREATE-INDEX CUSTOMER A1

This creates an index for all the items in attribute 1.

CREATE-INDEX INVOICE A1(TCUSTOMER;X;;1)

This creates an index in the invoice file for all the values found in the customer file attribute 1.

CREATE-INDEX CUSTOMER A1:2

This creates an index in the customer file with attribute 1 and attribute 2 concatenated together.

The limitation that exists with Advanced Pick B-trees is that you can not use the 'n(attribute name)' A-correlative type.

Advanced Pick B-trees are left justified index only. This causes problems if you are indexing dates. If you are looking at a date from left to right, then an internal date of 9867 is greater than 10234 since 9 is larger than 1. In order to allow numbers to be indexed they have to masked with zero's to make the number of characters of each value the same length.

CREATE-INDEX INVOICE A4(MR(%6))

Another limitation that exists with Advanced Pick B-trees is if you want to use the B-trees from ACCESS. If you have simple index key of 'A1' then an ACCESS statement using the dictionary name of the attribute would use the B-tree, but if you create a more complex key, such as 'A1:2', ACCESS will ignore the key.

Another limitation exists when you use more than one WITH statement in the ACCESS statement. Even if one of the WITH statements is a dictionary item that's index, the B-tree will be ignored when doing the select.

If you wish to select information from a complex key at TCL you will have to create a program to do so.

There are a two options that can be useful when working with CREATE-INDEX:

o - Overwrites existing indices

s - suppress the display of the running count as the index is created.

If you use an '*' instead of specifying an A-correlative, all the indices on the file will be processed.