Teradata查询Join Index主键列时未使用该索引问题咨询

Let's break down why your EMP_JI Join Index isn't being picked up by the optimizer for your select * from customerservice.employee where last_name = 'Brown' query:

1. Your Query Requests All Columns, But the Join Index Doesn't Cover Them #

Your Join Index only includes employee_number, department_number, last_name, manager_employee_number—but your query uses select *, which asks for every column in the employee table.

Teradata's optimizer won't use an index that doesn't cover all the data needed for the query, because it would have to go back to the base table to fetch the missing columns. This "bookmark lookup" adds extra overhead, so the optimizer decides it's more efficient to just read the base table directly instead of using the incomplete Join Index.

2. Single-Table Join Indexes Aren't Optimized for Non-Join Queries #

Join Indexes are primarily designed to speed up multi-table join operations. While you can create a single-table Join Index, Teradata's optimizer often prioritizes other index types (like secondary indexes) or direct base table access for simple single-table queries—especially when the index doesn't add clear value (like covering all needed columns).

3. Statistics Might Be Outdated #

If you haven't collected statistics on either the base employee table or the EMP_JI Join Index, the optimizer doesn't have accurate data to compare the cost of using the index vs. reading the base table.

Try running these commands to update statistics:

COLLECT STATISTICS ON customerservice.employee;
COLLECT STATISTICS ON a.EMP_JI;

After updating, re-run the explain to see if the optimizer changes its plan.

4. The Base Table's Primary Index Might Be More Efficient #

If the base employee table's Primary Index (PI) is already aligned with your filter (last_name), the optimizer might determine that accessing the base table directly is just as fast (or faster) than accessing the Join Index. Even if the base table's PI isn't last_name, if the table is small enough, a full table scan could be cheaper than navigating the index.

How to Fix This #

If you want the optimizer to use the Join Index, you have two main options:

• Restrict your query to only the columns in the index:

  EXPLAIN SELECT employee_number, department_number, last_name, manager_employee_number 
  FROM customerservice.employee 
  WHERE last_name = 'Brown';
  

• Modify the Join Index to cover all columns (note: this increases storage overhead):

  CREATE JOIN INDEX a.EMP_JI AS 
  SELECT a.* 
  FROM customerservice.employee a 
  PRIMARY INDEX (last_name);
  

内容的提问来源于stack exchange，提问作者tonyibm