Database Reference
In-Depth Information
Full index scan (CCTRY, BDATE, CNAME):
1
×
10 ms
+
1
,
000
,
000
×
0
.
01 ms
=
10 s
Ideal index (BDATE, CCTRY, CNAME):
1 × 10 ms + 100 × 0 . 01 ms = 11 ms
Index skip scan (CCTRY, BDATE, CNAME):
20 × 10 ms + 100 × 0 . 01 ms = 201 ms
BLOCK INDEXES
DB2 for LUW V8 provides an option of multidimensional clustering: Related
table rows are stored in blocks. Although this is a table design option, it is
interesting from the index point of view since it can sometimes be used as an
alternative to a fat index.
Let us assume an ORDER table for a company that sells 100 products in 50
countries. To make this table multidimensional, the following statement is added
to CREATE TABLE:
ORGANIZE BY DIMENSIONS PRODUCT AND COUNTRY
The table then consists of blocks , groups of consecutive pages (2-256 pages);
each block only contains rows that have the same values for all the dimensions:
Some blocks contain rows that relate to country 1 and product 1. A block index
on PRODUCT has pointers to all the blocks that contain rows for a particular
product; likewise a block index on COUNTRY has pointers to all the blocks that
contain rows that relate to a particular country. A compound block index is used
for INSERTs and normal indexes are used for other single-row operations.
When a SELECT contains WHERE PRODUCT = :PRODUCT AND
COUNTRY = :COUNTRY, the optimizer will probably choose an ANDing
operation on the two block indexes. Then the blocks containing the qualifying
rows are read. Almost all the touches are sequential.
The traditional alternative would be to have a clustered index, or a fat index,
beginning with PRODUCT and COUNTRY. After reorganization, practically all
touches are sequential, but the number of random touches increases after inserts.
The advantage of multidimensional clustering is that inserts do not degrade clus-
tering; there is no need to reorganize the table after a lot of inserts. The obvious
price is poor disk space utilization when the dimensions have a high cardinal-
ity and some values are rare; there may then be many blocks that contain only
one row.
DATA-PARTITIONED SECONDARY INDEXES
When a very large table is partitioned it may be desirable, for availability rea-
sons, to make all indexes on the table data partitioned. The ability to reorganize
partitions and their indexes in parallel is among the main benefits. DB2 for z/OS
V8 provides this option.
Search WWH ::




Custom Search