Communicator 3000 MPE/iX Release 6.0 (Platform Software Release C.60.00) (30216-90269)

172 Chapter 10
Technical Articles
IMAGE/SQL with TurboIMAGE/XL Enhancements
Although the B-Tree index on the related search item of the detail set is
not explicitly created using DBUTIL or DBSCHEMA, its definition is
entered in the SQL catalog. For example, if a B-Tree index is created on
a key item with paths to 16 detail data sets, a definition for a B-Tree
index for all 16 data sets will be entered as well. That is, this will result
into 17 (1+16) definitions, one for each data set.
If your key item or its related search item is split using the SPLIT
command of IMAGESQL, the definition for a B-Tree index on the split
item will not be entered. For an example, if a key item is split but the
related search item is not, the definition for the key item of the master
data set will not be entered, but the one for the search item will be.
The B-Tree indices can be viewed in the views, SYSTEM.INDEX and
CATALOG.INDEX, of the system catalog of the DBEnvironment.
Multiple index definitions on the same column can coexist and the SQL
optimizer derives the access plan based on the statistics present in the
system catalog. In other words, the key or search item of the set can
have a maximum of three index definitions. One will be a hash index
(only “=” operator permitted) automatically done by IMAGESQL at
ATTACH time, another can be a B-Tree index, and the third can be a
third-party index. It is recommended that both B-Tree index and
third-party index be not created on the same item as it will unnecessary
impact the performance (Optimizer calculates cost for each index).
The Optimizer derives an access plan and decides which index to use
and the proper order of operations.
The version of third-party software that supports the new B-Tree index
modes for DBFIND, DBCONTROL, and DBINFO, is required from both
third parties.
Third-Party Composite Indices
IMAGES/QL is enhanced to enter definitions for third-party composite
indices in the system catalog of the DBEnvironment at ATTACH time.
These composite indices can be on mixed data types, but must be on
FULL items. Information about all third-party indices, including
composite, is obtained from the third-party product, which is
subsequently used during ATTACH. Both SUPERDEX and OMNIDEX
do not provide information on the composite indices on substrings
(partial item) of items to IMAGE/SQL.
These indices can be viewed in SYSTEM.TPINDEX along with other
third-party indices.
At run-time, IMAGE/SQL may employ DBFIND mode 1 with “@;”
appended to the argument, or mode 11 with start and stop values, as
deemed appropriate.
Note that if you have multiple indices for the table, the SQL optimizer
has the control on the type of access, and the specific index to be used.