DBMS-Specific Indexing Options - Relational Database Index Design and the Optimizers - page 242

50 sets of B-trees

P,C

CNO,
IDATE,
IEUR

INO

INVOICE

500,000,000 rows
50 partitions

Figure 13.6 Data-partitioned
secondary indexes.

Let us assume that table INVOICE has 50 partitions as shown in Figure 13.6.

CREATE TABLE INVOICE
PARTITION BY (INO)
(PARTITION1 ENDING AT (10000000),
PARTITION2 ENDING AT (20000000)...

If index (CNO, IDATE, IEUR) is a data-partitioned index, 50 B-trees (index
partitions) are created, one for each table partition. Programmers need to be aware
of this because the minimum number of random index touches will be 50 if there
is no predicate for INO. The following SELECT would search all 50 B-trees.

SQL 13.5

SELECT INO, IEUR
FROM INVOICE
WHERE IDATE > :IDATE
ORDER BY IDATE

To reduce the number of B-trees searched, a predicate that specifies the relevant
INO range should be added:

SQL 13.6

SELECT INO, IEUR
FROM INVOICE
WHERE IDATE > :IDATE AND INO > :INO
ORDER BY IDATE

EXERCISES

8.1. Identify the index restrictions and advanced options of your current DBMS version.