ALLBASE/SQL Reference Manual (36216-90216)

70 Chapter2
Using ALLBASE/SQL
Understanding Data Access Paths
Understanding Data Access Paths
In creating a database, you must consider not only the arrangement of data, but also the
ways in which the data will be accessed during data manipulation operations. The four
following access methods are supported directly by ALLBASE/SQL:
• Serial access
• Indexed access
• Hashed access
• TID access
For indexed access, you must create a named index, or unique or referential constraint on
a table. Unique and referential constraints are supported by constraint indexes, which are
similar to B-tree indexes. For information on B-trees, refer to the section “Designing
Indexes” in the chapter “Logical Design” of the ALLBASE/SQL Database Administration
Guide
For hashed access, you must define a hash structure as you create the table.
By default, you do not explicitly choose an access method when you issue a query;
ALLBASE/SQL does this for you in a process known as optimization. Optimization
determines the best access path to the data for the query you have submitted. If a choice is
available among the different access methods--for example, if serial, indexed, and hashed
access are all possible for the same query--then the optimizer picks the best path. If no
other choice is available, the optimizer chooses serial access, also known as a sequential or
table scan. Serial access is always possible.
To override the access method chosen by the optimizer, use the SETOPT statement.
Serial Access
Serial access does not require the existence of any special object in addition to the table
itself. If ALLBASE/SQL chooses serial access when you issue a query, it starts reading
data from the first page in the table and continues to the end. Serial access is probably the
best access method when you intend to read all the data in the table. For example, an
application that updates every row in a table in exactly the same way would perform best
using a serial scan.
Indexed Access
Indexed access requires the use of a named index defined on specific columns in the table
to be accessed. Indexes can be plain, or they can be unique and/or clustering. Tables having
a unique index cannot have duplicate data values in the key column(s). A clustering
index causes rows with similar key values to be stored near to each other on disk when
this is possible. A table that is to use a clustering index should be loaded in the key order
specified by the clustering index. A clustering index can be defined on a unique or
referential constraint.
Whenever you issue a query, the query processor checks to see if an index exists for one or