B-trees provide additional support for obtaining a range of data, such as "all values over 10." Doug Meyer of the database lab noted during a recent meeting of the Client Server User Group that the prototype of B-trees shown at HP World executed a LIKE predicate pattern match against a 160,000-record database in five seconds -- a dramatic decrease from the three-minute timing without B-trees. HP is first offering B-tree power for key items only in master datasets.
HP says that B-trees can be specified by adding the keyword /INDEX in DBSCHEMA after the dataset. When the database is created, an index file will be created along with it.
HP has pointed out during the year-long discussion of B-trees that the feature won't be anywhere near as complete or powerful as products such as DISC's Omnidex or Bradmark's Superdex. HP is limiting the functionality primarily by keeping the scope of B-trees pared down to key items in master datasets. Omnidex and Superdex can create indexes on any field, either in detail or master datasets, or concatenate several fields into one index.
"Third-party indexing has much richer functionality," said Tien-You Chen, HP project manager in the CSY database lab. But HP is adding the feature, by using an external index file in KSAM/iX structure, to TurboIMAGE and IMAGE/SQL to offer the speed that comes with those basics. Existing keys in TurboIMAGE are hashed keys, which aren't useful when trying to get a range of data. That means those searches are now performed by a time-consuming serial scan of all records.
HP 3000 managers have long built KSAM files in batch mode to make ad hoc indexes available to applications. But system crashes often required rebuilding those KSAM files to repair pointer damage. What's more, the KSAM files had to be rebuilt manually after changes to the database. HP has engineered B-trees so DBPUTs, DBDELETEs or critical-item updates that affect B-tree key values will automatically update the B-trees. Also, the B-tree KSAM files are covered by the Transaction Manager, and IMAGE maintains the KSAM file to ensure recovery after almost all crashes.
IMAGE/SQL will be able to take advantage of B-tree improvements. Database administrators will have to run IMAGESQL to attach the TurboIMAGE database to the database environment (DBE). Attaching "registers the key index information into the SQL catalog file," Chen said, "so SQL knows that key has a B-tree index on it." This will permit the SQL SELECT statement that uses the LIKE predicate, or any kind of range predicate, to take advantage of the new indexes.