Chapter 15 Exercises: Index Design

These exercises progress from foundational recall through applied design and workload analysis. Difficulty ratings: [Beginner], [Intermediate], [Advanced], [Challenge].

Section A: B+ Tree Fundamentals (Exercises 1-7)

Exercise 1 — B+ Tree Terminology [Beginner]

Match each term to its correct definition:

Definitions to match: a) The number of levels in the B+ tree from root to leaf, inclusive. b) The bottom-level index page containing key values and pointers to actual data rows. c) The operation that occurs when a new entry must be added to a full index page. d) The single top-level page of the B+ tree that is the entry point for all searches. e) Row Identifier — a pointer encoding the page number and slot where the data row resides. f) The linked-list structure connecting leaf pages in key order, enabling range scans. g) An intermediate-level page containing separator keys and pointers to child pages.

Exercise 2 — Tree Height Calculation [Beginner]

A B+ tree index has the following characteristics: - Page size: 4 KB (4,096 bytes usable) - Non-leaf entry size: 20 bytes (key + pointer + overhead) - Leaf entry size: 50 bytes (key + RID + overhead) - Page header overhead: 96 bytes per page

a) Calculate the number of non-leaf entries per page. b) Calculate the number of leaf entries per page. c) What is the maximum number of rows that can be indexed in a tree of height 1? Height 2? Height 3? Height 4? d) If the table has 50 million rows, what is the minimum tree height?

Exercise 3 — Tracing a B+ Tree Lookup [Intermediate]

Given the following simplified B+ tree (height 3):

Root Page: [200 | 500 | 800]
             |      |      |      |
            P1     P2     P3     P4

P1 (non-leaf): [50 | 100 | 150]
P2 (non-leaf): [250 | 350 | 450]
P3 (non-leaf): [550 | 650 | 750]
P4 (non-leaf): [850 | 950]

Leaf pages: L1[1-49] L2[50-99] L3[100-149] L4[150-199]
            L5[200-249] L6[250-349] L7[350-449] L8[450-499]
            L9[500-549] L10[550-649] L11[650-749] L12[750-799]
            L13[800-849] L14[850-949] L15[950-999]

Trace the lookup path for each query. List every page read (root, non-leaf, leaf, data page).

a) WHERE CUSTOMER_ID = 375 b) WHERE CUSTOMER_ID = 50 c) WHERE CUSTOMER_ID BETWEEN 700 AND 810 d) How many I/O operations does each lookup require (assuming no pages are cached)?

Exercise 4 — Page Split Scenarios [Intermediate]

A leaf page in a B+ tree index currently contains entries for keys 100, 105, 110, 115, 120, 125, 130, 135, 140, 145, 150. The page is full.

a) A new row with key 122 is inserted. Describe what happens during the page split. Which entries go to the new page? b) After the split, a new row with key 117 is inserted. Does this cause another split? Which page receives the entry? c) Now consider a monotonically increasing key (auto-increment). All new entries have keys larger than any existing key. Describe the pattern of page splits. Why is this pattern more efficient than random inserts?

Exercise 5 — Tree Height vs. Table Size [Beginner]

Fill in the blanks:

a) A B+ tree of height 3 with 200 entries per non-leaf page and 100 entries per leaf page can index up to __ rows. b) Adding one level (going from height 3 to height 4) multiplies the maximum capacity by a factor of _. c) A table grows from 1 million rows to 100 million rows. The B+ tree height increases from to ___ (assume 200 non-leaf entries per page and 100 leaf entries per page). d) Why does this logarithmic growth matter for performance?

Exercise 6 — Buffer Pool Impact [Intermediate]

An index on a table with 20 million rows has the following profile: - Tree height: 3 - Root page: 1 page - Non-leaf pages: 80 pages (level 2) - Leaf pages: 16,000 pages - Data pages accessed per lookup: 1

Assume the buffer pool for this index can hold 500 pages.

a) Which index pages will almost certainly remain in the buffer pool? Why? b) For a random point lookup, what is the expected number of physical I/O operations? c) If 90% of queries access 10% of the key range (a hot spot), how does this affect buffer pool hit rates for leaf pages?

Exercise 7 — Range Scan Cost Estimation [Advanced]

The TRANSACTION table has 200 million rows and an index on TRANSACTION_DATE. The tree height is 4. There are approximately 548,000 transactions per day.

a) Estimate the number of leaf pages that contain entries for one day's transactions. Assume 100 leaf entries per page. b) To retrieve all transactions for a 30-day month, how many leaf pages must DB2 read? c) If the cluster ratio on TRANSACTION_DATE is 95%, approximately how many data pages must DB2 read for the 30-day query? Assume 10 rows per data page. d) If the cluster ratio drops to 40%, how does the data page count change? What is the performance implication?

Section B: Index Creation and Design (Exercises 8-16)

Exercise 8 — CREATE INDEX Syntax [Beginner]

Write the CREATE INDEX statement for each scenario:

a) A unique index on the EMPLOYEE table, column EMPLOYEE_ID. b) A non-unique index on the EMPLOYEE table, column DEPARTMENT_ID. c) A composite index on the ORDER table, columns (CUSTOMER_ID, ORDER_DATE DESC). d) A unique index on the PRODUCT table, column PRODUCT_CODE, with INCLUDE columns PRODUCT_NAME and PRICE (LUW). e) A clustering index on the INVOICE table, column INVOICE_DATE (z/OS syntax).

Exercise 9 — Identifying Covering Indexes [Intermediate]

Given the following indexes on the EMPLOYEE table:

IX_EMP_PK:       (EMPLOYEE_ID) -- unique
IX_EMP_DEPT:     (DEPARTMENT_ID, HIRE_DATE)
IX_EMP_NAME:     (LAST_NAME, FIRST_NAME) INCLUDE (EMPLOYEE_ID, EMAIL)
IX_EMP_SALARY:   (DEPARTMENT_ID) INCLUDE (SALARY)

For each query, determine whether index-only access is possible. If yes, identify which index enables it.

a) SELECT EMPLOYEE_ID FROM EMPLOYEE WHERE EMPLOYEE_ID = 12345 b) SELECT LAST_NAME, FIRST_NAME, EMAIL FROM EMPLOYEE WHERE LAST_NAME = 'SMITH' c) SELECT DEPARTMENT_ID, AVG(SALARY) FROM EMPLOYEE GROUP BY DEPARTMENT_ID d) SELECT * FROM EMPLOYEE WHERE DEPARTMENT_ID = 100 e) SELECT EMPLOYEE_ID, LAST_NAME FROM EMPLOYEE WHERE LAST_NAME LIKE 'J%' ORDER BY LAST_NAME f) SELECT DEPARTMENT_ID, HIRE_DATE FROM EMPLOYEE WHERE DEPARTMENT_ID = 100 AND HIRE_DATE > '2020-01-01'

Exercise 10 — Column Order in Composite Indexes [Intermediate]

For each query, design the optimal composite index. Explain why you chose that column order.

a) SELECT * FROM ORDER WHERE CUSTOMER_ID = ? AND STATUS = 'SHIPPED' AND ORDER_DATE > '2025-01-01'

b) SELECT PRODUCT_ID, SUM(QUANTITY) FROM ORDER_ITEM WHERE PRODUCT_ID = ? AND ORDER_DATE BETWEEN '2025-01-01' AND '2025-12-31' GROUP BY PRODUCT_ID

c) SELECT * FROM EMPLOYEE WHERE DEPARTMENT_ID = ? ORDER BY LAST_NAME, FIRST_NAME

d) SELECT ACCOUNT_ID, BALANCE FROM ACCOUNT WHERE BRANCH_ID = ? AND ACCOUNT_TYPE = 'SAVINGS' ORDER BY BALANCE DESC

Exercise 11 — Left Prefix Rule Analysis [Intermediate]

Given the composite index: IX_ORDER: (CUSTOMER_ID, ORDER_DATE, STATUS)

For each query, determine: (1) Can the index be used for a matching scan? (2) How many columns are "matching"? (3) Would a different index be more efficient?

a) WHERE CUSTOMER_ID = 100 b) WHERE CUSTOMER_ID = 100 AND ORDER_DATE = '2025-03-01' c) WHERE CUSTOMER_ID = 100 AND STATUS = 'SHIPPED' d) WHERE ORDER_DATE = '2025-03-01' e) WHERE STATUS = 'SHIPPED' f) WHERE CUSTOMER_ID = 100 AND ORDER_DATE > '2025-01-01' AND STATUS = 'SHIPPED' g) WHERE CUSTOMER_ID IN (100, 200, 300) AND ORDER_DATE BETWEEN '2025-01-01' AND '2025-03-31'

Exercise 12 — Clustering Index Selection [Advanced]

The SALES table has 100 million rows with the following columns: SALE_ID, STORE_ID, SALE_DATE, PRODUCT_ID, QUANTITY, AMOUNT, CUSTOMER_ID.

The workload consists of: - 60% of queries: WHERE STORE_ID = ? AND SALE_DATE BETWEEN ? AND ? (store managers viewing daily/weekly sales) - 25% of queries: WHERE PRODUCT_ID = ? AND SALE_DATE BETWEEN ? AND ? (product managers viewing product performance) - 10% of queries: WHERE CUSTOMER_ID = ? (customer purchase history) - 5% of queries: Full table scans for monthly aggregation reports

a) Which column(s) should the clustering index use? Justify your choice. b) Design the complete set of indexes for this table (including the clustering index). c) For the access pattern that does NOT benefit from clustering, what alternative strategies could mitigate the performance impact?

Exercise 13 — INCLUDE Column Strategy [Advanced]

The PRODUCT table has columns: PRODUCT_ID, PRODUCT_NAME, CATEGORY, PRICE, WEIGHT, DESCRIPTION (CLOB), SUPPLIER_ID, CREATED_DATE, STATUS.

The top 5 queries by frequency are:

1. SELECT PRODUCT_ID, PRODUCT_NAME, PRICE FROM PRODUCT WHERE CATEGORY = ? ORDER BY PRODUCT_NAME (80% of reads)
2. SELECT * FROM PRODUCT WHERE PRODUCT_ID = ? (10% of reads)
3. SELECT PRODUCT_ID, PRODUCT_NAME, CATEGORY, PRICE FROM PRODUCT WHERE SUPPLIER_ID = ? (5% of reads)
4. SELECT CATEGORY, COUNT(*), AVG(PRICE) FROM PRODUCT WHERE STATUS = 'ACTIVE' GROUP BY CATEGORY (3% of reads)
5. SELECT PRODUCT_ID FROM PRODUCT WHERE PRICE BETWEEN ? AND ? (2% of reads)

a) Design an index strategy that achieves index-only access for queries 1 and 3. b) Can query 4 be made index-only? If so, how? c) What is the total additional storage cost of your INCLUDE columns compared to key-only indexes? (Assume PRODUCT_NAME is VARCHAR(200), CATEGORY is VARCHAR(50), PRICE is DECIMAL(10,2), SUPPLIER_ID is INTEGER, STATUS is CHAR(1), PRODUCT_ID is INTEGER.)

Exercise 14 — Redundant Index Identification [Intermediate]

A table has the following indexes:

IX_A:  (CUSTOMER_ID)
IX_B:  (CUSTOMER_ID, ACCOUNT_TYPE)
IX_C:  (ACCOUNT_TYPE, CUSTOMER_ID)
IX_D:  (CUSTOMER_ID, ACCOUNT_TYPE, STATUS)
IX_E:  (ACCOUNT_TYPE)
IX_F:  (CUSTOMER_ID) INCLUDE (ACCOUNT_TYPE, STATUS)

a) Which indexes are potentially redundant? Explain why. b) For each redundant index, what query or situation might justify keeping it despite the redundancy? c) After consolidation, what is your recommended set of indexes?

Exercise 15 — Anti-Pattern Diagnosis [Intermediate]

For each scenario, identify the index anti-pattern and propose a fix:

a) Query: SELECT * FROM CUSTOMER WHERE UPPER(LAST_NAME) = 'SMITH' Index: IX_CUST_LAST (LAST_NAME)

b) Query: SELECT * FROM ORDER WHERE ORDER_DATE LIKE '%2025%' Index: IX_ORDER_DATE (ORDER_DATE)

c) Query: SELECT * FROM ACCOUNT WHERE ACCOUNT_NUMBER = 12345 Column ACCOUNT_NUMBER is defined as CHAR(10).

d) A table with 50 columns has 23 indexes. INSERT operations take 200ms each.

e) Query: SELECT * FROM EMPLOYEE WHERE STATUS = 'ACTIVE' Index: IX_EMP_STATUS (STATUS) — STATUS has only 3 possible values: 'ACTIVE', 'INACTIVE', 'TERMINATED'. 75% of employees are 'ACTIVE'.

Exercise 16 — Expression-Based Index Design [Advanced]

Design expression-based indexes (or generated column + index equivalents for z/OS) for the following scenarios:

a) Queries frequently search by the year portion of a date: WHERE YEAR(HIRE_DATE) = 2024

b) Queries search for customers by a case-insensitive email match: WHERE LOWER(EMAIL) = ?

c) Queries calculate account age: WHERE DAYS(CURRENT_DATE) - DAYS(OPEN_DATE) > 365

d) Queries search for a computed value: WHERE AMOUNT * EXCHANGE_RATE > 10000

For each, provide both the LUW expression-based index syntax and the z/OS generated column approach.

Section C: Workload Analysis and Design (Exercises 17-25)

Exercise 17 — Workload-Driven Index Design [Advanced]

An e-commerce ORDER table has the following schema:

CREATE TABLE ORDERS (
    ORDER_ID        BIGINT NOT NULL PRIMARY KEY,
    CUSTOMER_ID     INTEGER NOT NULL,
    ORDER_DATE      DATE NOT NULL,
    SHIP_DATE       DATE,
    STATUS          VARCHAR(20) NOT NULL,
    TOTAL_AMOUNT    DECIMAL(12,2),
    SHIPPING_ADDR_ID INTEGER,
    PAYMENT_METHOD  VARCHAR(30),
    PROMO_CODE      VARCHAR(20)
);

The table has 50 million rows. The workload:

Design a complete index strategy. For each index, state which queries it supports and why you chose the specific columns and order.

Exercise 18 — Index Overhead Estimation [Advanced]

Using your index design from Exercise 17, estimate the total overhead:

a) Calculate the approximate size of each index (assume average key widths and 4 KB pages). b) For each INSERT, how many additional page writes are required (one per index)? c) If the table receives 20,000 inserts per day, how many additional page writes per day are attributable to indexes? d) Estimate the impact on REORG time: if a REORG of the table alone takes 2 hours, and each index adds approximately 30% to the REORG time, how long does the full REORG take?

Exercise 19 — [z/OS] DPSI vs. NPSI Decision [Advanced]

A range-partitioned TRANSACTION table on z/OS has 12 monthly partitions (one per month). The table has secondary indexes on STATUS, MERCHANT_ID, and TRANSACTION_TYPE.

For each secondary index, the following query patterns exist:

• STATUS: 90% of queries include a date range (which maps to partition key). 10% do not.
• MERCHANT_ID: 50% of queries include a date range. 50% do not.
• TRANSACTION_TYPE: 20% of queries include a date range. 80% do not.

a) For each index, recommend DPSI or NPSI. Justify each choice. b) For the DPSI indexes, what is the performance impact on queries that do NOT include the partition key predicate? c) How does your recommendation change if the table grows to 120 partitions (10 years of monthly data)?

Exercise 20 — Index Consolidation Exercise [Challenge]

A legacy system has the following indexes on the CUSTOMER table (20 columns, 5 million rows):

IX1:  (CUSTOMER_ID)                    -- PK
IX2:  (LAST_NAME)
IX3:  (LAST_NAME, FIRST_NAME)
IX4:  (FIRST_NAME, LAST_NAME)
IX5:  (EMAIL)
IX6:  (EMAIL) INCLUDE (CUSTOMER_ID, LAST_NAME)
IX7:  (PHONE_NUMBER)
IX8:  (SSN)
IX9:  (ZIP_CODE)
IX10: (ZIP_CODE, CITY)
IX11: (CITY, STATE)
IX12: (STATE)
IX13: (CUSTOMER_ID, STATUS)
IX14: (STATUS)
IX15: (CREATED_DATE)
IX16: (CREATED_DATE, STATUS)
IX17: (LAST_LOGIN_DATE)

The DBA wants to reduce from 17 indexes to no more than 8.

a) Identify all redundant indexes and explain the redundancy. b) Identify indexes on low-cardinality columns that are unlikely to be useful. c) Propose a consolidated set of 8 or fewer indexes. For each, list the queries it supports. d) What risks does the consolidation introduce? How would you mitigate them?

Exercise 21 — Monitoring and Maintenance [Intermediate]

Write SQL queries (for DB2 LUW) to answer each question:

a) List all indexes on the MERIDIAN.TRANSACTION table, showing index name, columns, uniqueness, and cluster ratio. b) Find all indexes in the MERIDIAN schema that have never been used (LASTUSED is NULL). c) Find the 10 largest indexes (by number of leaf pages) in the MERIDIAN schema. d) Find indexes with a cluster ratio below 80%. e) Find all tables in the MERIDIAN schema that have more than 5 indexes.

Exercise 22 — Covering Index Design Challenge [Challenge]

The following three queries must ALL achieve index-only access. Design the minimum number of indexes to accomplish this.

-- Query A
SELECT CUSTOMER_ID, LAST_NAME, FIRST_NAME, EMAIL
FROM CUSTOMER
WHERE LAST_NAME = ? AND FIRST_NAME = ?;

-- Query B
SELECT CUSTOMER_ID, LAST_NAME, EMAIL, PHONE
FROM CUSTOMER
WHERE EMAIL = ?;

-- Query C
SELECT LAST_NAME, FIRST_NAME, PHONE
FROM CUSTOMER
WHERE PHONE = ?;

a) Can all three queries be covered by a single index? Why or why not? b) What is the minimum number of indexes required? c) Design the indexes. Show that each query achieves index-only access.

Exercise 23 — Index Impact on Write Performance [Advanced]

A table receives 5,000 INSERTs per second during peak hours. The table currently has 3 indexes and each INSERT takes an average of 0.8ms.

a) A new index is proposed. Assuming each index adds approximately 0.2ms to each INSERT, what is the new INSERT time? b) At the new INSERT time, can the system still sustain 5,000 INSERTs/second on a single thread? (Assume a single thread can process one INSERT at a time.) c) If the proposed index is a wide covering index (8 columns), how might it affect page split frequency compared to a narrow 2-column index? d) The new index would improve a SELECT query from 50ms to 2ms. The SELECT runs 500 times per day. Calculate the daily time saved on SELECTs vs. the daily time added to INSERTs (assuming 8 peak hours at 5,000 inserts/sec, 16 off-peak hours at 500 inserts/sec).

Exercise 24 — Real-World Scenario Analysis [Challenge]

Meridian National Bank's TRANSACTION table has grown to 1 billion rows. The nightly batch that generates daily account statements is taking 6 hours, violating the 4-hour batch window. The batch query is:

SELECT T.ACCOUNT_ID, T.TRANSACTION_DATE, T.DESCRIPTION,
       T.AMOUNT, T.RUNNING_BALANCE
FROM TRANSACTION T
WHERE T.TRANSACTION_DATE = CURRENT DATE - 1 DAY
ORDER BY T.ACCOUNT_ID, T.TRANSACTION_DATE;

Current indexes: - IX_TXN_PK: (TRANSACTION_ID) -- unique - IX_TXN_ACCT_DATE: (ACCOUNT_ID, TRANSACTION_DATE DESC) CLUSTER - IX_TXN_DATE: (TRANSACTION_DATE) - IX_TXN_REF: (REFERENCE_NUMBER) -- unique

The cluster ratio on IX_TXN_ACCT_DATE is 72%.

a) Explain why the batch is slow, considering the clustering index choice and the cluster ratio. b) Propose three different solutions, each with trade-offs. Consider: new indexes, REORG strategy, query rewrite, partitioning. c) Which solution would you recommend? Justify your choice considering the 24/7 OLTP workload that runs on the same table.

Exercise 25 — Full Database Index Strategy [Challenge]

You are designing the index strategy for a hospital patient management system with the following tables:

• PATIENT (PATIENT_ID, SSN, LAST_NAME, FIRST_NAME, DOB, GENDER, ADDRESS, PHONE, EMAIL, INSURANCE_ID, CREATED_DATE)
• APPOINTMENT (APPT_ID, PATIENT_ID, DOCTOR_ID, APPT_DATE, APPT_TIME, DEPARTMENT, STATUS, NOTES)
• MEDICAL_RECORD (RECORD_ID, PATIENT_ID, VISIT_DATE, DOCTOR_ID, DIAGNOSIS_CODE, TREATMENT, NOTES_CLOB)
• PRESCRIPTION (RX_ID, PATIENT_ID, DOCTOR_ID, MEDICATION_ID, PRESCRIBED_DATE, DOSAGE, FREQUENCY, STATUS)
• BILLING (BILL_ID, PATIENT_ID, APPT_ID, AMOUNT, INSURANCE_AMOUNT, PATIENT_AMOUNT, STATUS, DUE_DATE)

Key query patterns: 1. Patient lookup by SSN (emergency department) 2. Patient search by name (registration desk) 3. All appointments for a patient (patient portal) 4. Today's appointments for a doctor (doctor's schedule) 5. All medical records for a patient (doctor reviewing history) 6. Active prescriptions for a patient (pharmacy) 7. Unpaid bills for a patient (billing department) 8. All bills due within next 30 days (collections batch)

Design the complete index strategy. For each index, state: the table, columns, whether it is clustering, whether INCLUDE columns are used, and which query pattern(s) it supports. Aim for no more than 4 indexes per table.

Section D: Conceptual Understanding (Exercises 26-30)

Exercise 26 — Explain Like I'm a Manager [Beginner]

Your CTO asks: "Why can't we just index every column? Storage is cheap."

Write a 200-word explanation, suitable for a non-technical executive, that explains why over-indexing is harmful. Use the Meridian Bank TRANSACTION table as an example.

Exercise 27 — B+ Tree vs. Hash Index [Intermediate]

Some databases offer hash indexes, which provide O(1) lookup for equality predicates.

a) Why does DB2 use B+ trees instead of (or in addition to) hash indexes? b) List three types of queries where a B+ tree outperforms a hash index. c) Are there scenarios where a hash index would be preferable? (Think about the types of predicates.)

Exercise 28 — The Clustering Trade-Off Debate [Advanced]

Two DBAs disagree about the clustering index for Meridian's TRANSACTION table:

• DBA A wants to cluster on (ACCOUNT_ID, TRANSACTION_DATE) for OLTP performance.
• DBA B wants to cluster on (TRANSACTION_DATE) for batch reporting performance.

Write a 300-word analysis of both positions. Then state which you would choose and why, given that Meridian is primarily an OLTP system with nightly batch reporting.

Exercise 29 — Index Design Review Checklist [Intermediate]

Create a checklist (10-15 items) that a DBA should work through before deploying a new index in production. Consider: workload analysis, redundancy check, overhead estimation, testing, rollback plan, and monitoring.

Exercise 30 — Predicting Optimizer Behavior [Advanced]

For each scenario, predict whether the DB2 optimizer will use the available index or choose a tablespace scan. Explain your reasoning using concepts of selectivity, cluster ratio, and I/O cost.

a) Table: 1 million rows. Index on STATUS (3 distinct values). Query: WHERE STATUS = 'PENDING' (1% of rows).

b) Table: 1 million rows. Index on STATUS (3 distinct values). Query: WHERE STATUS = 'ACTIVE' (85% of rows).

c) Table: 10 million rows. Index on CREATED_DATE. Cluster ratio: 99%. Query: WHERE CREATED_DATE BETWEEN '2025-01-01' AND '2025-01-31' (3% of rows).

d) Table: 10 million rows. Index on CREATED_DATE. Cluster ratio: 15%. Query: WHERE CREATED_DATE BETWEEN '2025-01-01' AND '2025-01-31' (3% of rows).

e) Table: 100 rows. No index on any column. Query: WHERE CUSTOMER_ID = 5. Will the optimizer even consider an index scan?

Return to Chapter 15 | Continue to Quiz