In DB2 query optimization, how can the DB2 optimizer make use of multiple indexes defined on the same table?

Introduction / Context:
Modern DB2 optimizers are capable of sophisticated index usage strategies. This interview question tests whether the candidate is aware that DB2 can use more than one index on a table in advanced ways, such as index screening and index ANDing or ORing, rather than being limited to just one index or a simple full table scan.

Given Data / Assumptions:

• A DB2 table has multiple indexes defined on different sets of columns.
• A query includes predicates that can potentially make use of more than one index.
• The DB2 optimizer is free to choose the best available access path.

Concept / Approach:
DB2 typically selects a primary index to drive the access to a table based on cost estimates. It can then refine the set of qualifying rows by applying additional index based filtering, known as index screening, or by combining row sets from multiple indexes using index ANDing or ORing. This ability allows DB2 to exploit multiple indexes to satisfy complex predicates efficiently. The correct option must explicitly describe this capability rather than restricting DB2 to a single index or ignoring indexes altogether.

Step-by-Step Solution:
Step 1: Recall that the optimizer examines all available indexes and computes cost estimates for potential access paths. Step 2: It chooses one index as the main driver, for example an index on CUST_ID for a query that filters by customer. Step 3: If there are other predicates on different columns, DB2 can use additional indexes to help screen rows or perform index ANDing or ORing. Step 4: Index ANDing combines row identifiers that satisfy multiple index conditions, while index ORing can combine lists from different indexes when predicates are joined with OR. Step 5: The presence of multiple useful indexes therefore enhances the optimizer's flexibility rather than restricting it.

Verification / Alternative check:
Explain plans generated by DB2 utilities often show the use of index screening or index ANDing strategies. These plan descriptions confirm that more than one index can influence how rows are selected, even though a single index may be named as the primary access path. Observing such plans in real workloads validates the description in option A.

Why Other Options Are Wrong:
Option B is wrong because DB2 is not limited to a single index per query; advanced optimization techniques support multiple index usage.
Option C is wrong because DB2 does not ignore indexes simply because there are many; it evaluates their usefulness for specific queries.
Option D is wrong because multiple indexes do not force table level locking or prevent optimization; they can actually improve performance and concurrency.

Common Pitfalls:
A common misconception is that adding more indexes always helps performance. In reality, too many unused or overlapping indexes can increase maintenance overhead without providing benefit. Another pitfall is assuming that DB2 always behaves like simpler databases that use only one index; understanding index screening and index ANDing enables better index design for complex queries.

Final Answer:
DB2 can choose one index as the primary access path and use additional indexes for index screening or index ANDing or ORing to refine the qualifying rows.