Teradata技术问询：主键索引查询场景及单表Join Index索引使用疑问

Hey there! Let's tackle your Teradata questions with clear, practical explanations tailored to how Teradata's architecture works.

Teradata's Primary Index is the backbone of its MPP (Massively Parallel Processing) performance—it determines how data is distributed across AMPs (Access Module Processors). Here are the key scenarios where leveraging the PI makes sense:

• Exact match filtering on PI columns: When you use an = condition on the PI column(s), Teradata calculates the hash of the value and directly routes the query to the specific AMP(s) holding that data. This avoids a full table scan and drastically speeds up response times (e.g., WHERE employee_number = 12345).
• Joining tables on their PI columns: If two tables are joined using their respective PI columns, Teradata can perform an AMP-local join—no data needs to be redistributed across the cluster. This eliminates the overhead of data movement and is the most efficient way to join large tables.
• Retrieving large datasets with even distribution: When pulling large volumes of data, a well-designed PI ensures data is evenly spread across AMPs. This lets all AMPs process their portion of the data in parallel, preventing bottlenecks from data skew.
• Prioritizing fast response for critical queries: For queries that need immediate results (like real-time lookups), targeting the PI with exact matches is the optimal approach—you skip the time-consuming full table scan entirely.

Let's break down your specific scenario first. You created a single-table Join Index (JI) like this:

CREATE JOIN INDEX CustomerService.EMP_JI 
AS SELECT employee_number , department_number, employee.last_name, manager_employee_number 
FROM customer.employee; -- Assuming your base table is customer.employee

When you run WHERE employee_number = '123', the PI is used, but WHERE employee_number LIKE '%123' doesn't leverage it. Here's why:

Teradata's PI is hash-based #

The Primary Index uses a hash function to assign rows to AMPs. This only works when you provide an exact value—Teradata can compute the hash of that value and know exactly which AMP to query. For wildcard queries like %123, there's no way to compute a hash for a partial value. Teradata can't predict which AMPs might hold rows matching the pattern, so it has to scan all AMPs (a full table/join index scan).

Single-table Join Indexes don't change this behavior #

A single-table JI is essentially a precomputed, stored subset of the base table's data. Its data distribution is typically tied to the base table's PI (unless you explicitly define a different PI for the JI). Even with the JI, the same hash-based limitation applies: fuzzy matches can't use the PI to target specific AMPs. The JI might make the scan faster (since it's smaller than the base table), but it still won't trigger PI usage.

What can you do to optimize wildcard queries? #

If you frequently run suffix/prefix wildcard queries, consider these options:

• Create a Secondary Index (SI) on the column: A SI is a non-hashed index, so Teradata can use it to narrow down rows matching the wildcard pattern (though it's still not as fast as PI exact matches).
• Use prefix wildcards only ('123%'): If your use case allows, prefix matches can sometimes be optimized with a SI, and in some cases, Teradata can use a PI if the column is ordered (but this isn't guaranteed).
• Explore Teradata's Full-Text Indexing: For more complex string searches, full-text indexes are designed to handle wildcard and pattern-based queries efficiently.

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