时间戳

首页 > 解决方案 > teradata：何时使用主索引查询表？

indexing - teradata：何时使用主索引查询表？

问题描述

我在单个表上创建了一个连接索引，当我用 where 子句查询表时，一个用“=”，另一个用“%”，但似乎第一个查询使用主索引，第二个查询不使用它，不明白，第二次查询主索引列，为什么第二次查询不使用主索引？

单表的连接索引如下：

CREATE JOIN INDEX CustomerService.EMP_JI AS
SELECT employee_number ,
       department_number,
       employee.last_name,
       manager_employee_number
FROM customerservice.employee
PRIMARY INDEX ( last_name );

第一个解释，

explain sel * from customerservice.employee where last_name = 'tony';


1) First, we do a single-AMP RETRIEVE step from
 CustomerService.EMP_JI by way of the primary index
 "CustomerService.EMP_JI.last_name = 'tony '" with no residual
 conditions into Spool 2 (group_amps), which is redistributed by
 the hash code of (CustomerService.EMP_JI.employee_number) to few
 AMPs.  Then we do a SORT to order Spool 2 by row hash.  The size
 of Spool 2 is estimated with low confidence to be 1 row (45 bytes).
 The estimated time for this step is 0.01 seconds.
 2) Next, we do a group-AMPs JOIN step from customerservice.employee
 by way of a RowHash match scan with no residual conditions, which
 is joined to Spool 2 (Last Use) by way of a RowHash match scan.
 customerservice.employee and Spool 2 are joined using a merge join,
 with a join condition of ("Field_1025 =
 customerservice.employee.employee_number").  The result goes into
 Spool 1 (group_amps), which is built locally on that AMP.  The
 size of Spool 1 is estimated with low confidence to be 1 row (143
 bytes).  The estimated time for this step is 0.03 seconds.
 3) Finally, we send out an END TRANSACTION step to all AMPs involved
 in processing the request.
 -> The contents of Spool 1 are sent back to the user as the result of
 statement 1.  The total estimated time is 0.04 seconds.

第二个解释，

explain sel * from customerservice.employee where last_name like '%tony';


1) First, we lock a distinct customerservice."pseudo table" for read
 on a RowHash to prevent global deadlock for
 customerservice.employee.
2) Next, we lock customerservice.employee for read.
3) We do an all-AMPs RETRIEVE step from customerservice.employee by
 way of an all-rows scan with a condition of (
 "customerservice.employee.last_name LIKE '%tony'") into Spool 1
 (group_amps), which is built locally on the AMPs.  The size of
 Spool 1 is estimated with low confidence to be 2 rows (286 bytes).
 The estimated time for this step is 0.07 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
 in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
 statement 1.  The total estimated time is 0.07 seconds.

标签： indexingteradata

解决方案

where 子句需要具有相等条件（=）才能访问主索引，因为主索引将行分布在主索引的哈希值上。

SQL 中的 Teradata Optimizer Equal vs Like

推荐阅读