需要更好的选择 - 外连接 32 次到同一张表

Question

我有一个令人讨厌的 SQL 查询问题，我希望得到一个优雅的解决方案的帮助。我试图避免对同一个表进行 32 个左外连接。

数据库是 Teradata。

我有一个包含 1400 万条记录和 33 列的表。主键（我们称之为 Trans_Id）和 32 个编码字段（我们称之为encoded_1 ...encoded_32）。像这样的事情：

CREATE SET TABLE BigTable ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT
     (
      TRANS_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      ENCODED_1 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      ENCODED_2 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      ENCODED_3 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      ...
      ENCODED_32 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC )
PRIMARY INDEX ( TRANS_ID );

我还有一个包含编码/解码值的表。假设此表中有 100 条记录。

CREATE SET TABLE LookupTable ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT
     (
      UNIQ_PK { just random numbers }
      ENCODED_VAR VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      DECODED_DESC VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( UNIQ_PK );

我想避免像这样的令人讨厌的连接（我使用省略号而不是显示所有 32 个外部连接）：

SELECT 
TRANS_ID
, a.ENCODED_1
, b1.DECODED_DESC DECODED_DESC_1
, a.ENCODED_2
, b2.DECODED_DESC DECODED_DESC_2
...
, a.ENCODED_31
, b31.DECODED_DESC DECODED_DESC_31
, a.ENCODED_32
, b32.DECODED_DESC DECODED_DESC_32
FROM BigTable a
LEFT OUTER JOIN LookupTable b1 ON a.ENCODED_1 = b1.ENCODED
LEFT OUTER JOIN LookupTable b2 ON a.ENCODED_2 = b1.ENCODED
...
LEFT OUTER JOIN LookupTable b31 ON a.ENCODED_31 = b31.ENCODED
LEFT OUTER JOIN LookupTable b32 ON a.ENCODED_32 = b32.ENCODED

任何帮助将不胜感激。我有一种感觉，外部连接 14M 记录 32 次并不是执行此操作的有效方法！

Answer 1

您可以创建一个函数，将 VARCHAR(10)encoded_var 作为参数并返回 VARCHAR(50)decoded_desc，然后您的选择将如下所示：

SELECT TRANS_ID,
     ENCODED_1, somefunc(ENCODED_1) AS DECODED_DESC_1,
     ENCODED_2, somefunc(ENCODED_2) AS DECODED_DESC_2,
     etc.

根据您计划一次返回的行数，这是可行的。

Answer 2

如果encoded_1、encoded_2等都被用作同一个表的查找键，听起来它们都是“相同的想法”。但我的第一个想法是，在这种情况下更好的设计是：

big_table (trans_id, var_id, encoded_var)
lookup_table (encoded_var, decoded_desc)

然后查询就变成：

select trans_id, var_id, encoded_var, decoded_desc
from big_table
join lookup_table on lookup_table.encoded_var=big_table.encoded_var

我不知道这是否是真正的字段名称，或者您只是想省略不相关的细节。您可能会在这里遗漏相关细节。 encoded_1 和encoded_2 等有什么区别？如果它们可以互换，则没有理由为它们设置单独的字段。确实，它会带来很多问题。即使存在语义差异，如果它们都使用相同的查找表，则它们也必须来自同一域。

例如，几年前，我开发了一个系统来管理我们组织制作和使用的技术手册。每本手册有 3 名管理员。 （负责处理预算和时间表的行政经理，负责跟踪谁需要副本并确保他们获得副本的库存经理，以及负责实际文本的内容经理。）但他们都来自同一份人员名单，通常同一个人会担任多个角色，或者可能针对不同的手册担任不同的角色。因此，我们制作了一个包含 ID、姓名、电子邮件地址等的“人员”表，然后在基本手动记录中我创建了 3 列，每个经理类型对应一列。

这是一个巨大的错误。我应该做的是创建一个单独的表，其中包含手动 id、经理类型 id 和人员 id，然后为 3 种经理类型创建 3 条记录，而不是一条记录中的 3 个字段。

为什么？对于三列，我遇到了您所描述的相同问题，尽管规模较小：我必须从手动表连接到人员表三次。像“鲍勃·史密斯负责哪些书？”这样的查询需要一个令人惊讶的复杂查询，就像使用单个列一样，

select ... whatever ...
from manual
join person p1 on p1.person_id=manual.admin_id
join person p2 on p2.person_id=manual.stockmanager_id
join person p3 on p3.person_id=manual.contentmanager_id
where p1.name='Bob Smith'
 or p2.name='Bob Smith'
 or p3.name='Bob Smith'

它本来就很简单

select ... whatever ...
from manual
join manual_manager on manual_manager.manual_id=manual.manual_id
join person on person.person_id=manual_manager.person_id
where person.name='Bob Smith'"

。随着所有的重复，毫不奇怪的是，有几次程序员不小心只检查了 2 个字段而不是所有 3 个字段。使用 1字段此错误是不可能的。对于 3 个字段，如果我们添加第四种类型的管理器，我们将不得不添加另一列，然后更改查看这些字段的每个查询。如果有 1 个字段，我们可能不会。等等。

对于 3 个字段，我们需要 3 个索引，并且还有其他性能影响。

我怀疑同样的想法也适用于你。

如果您的 32 个字段都是完全可互换的，那么该表只需要一个序列号即可创建唯一的 pk。如果它们之间存在一些差异，那么您可以创建一个代码来区分它们。

Answer 3

首先，我会将 LookUp 表上的 PI 更改为 Encoded_Var。您必须在每个 Encoded_Var 列上重新分配大表才能连接到 LookUp 表。为什么每次都必须重新分配 LookUp 表呢？

您的表设计是否有原因不接近

CREATE SET TABLE BigTable ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT
     (
      TRANS_ID    VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      ENCODED_VAR VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
UNIQUE PRIMARY INDEX ( TRANS_ID, ENCODED_VAR );

这将在 trans_id 和encoded_var 之间建立更合适的 1:M 关系。除非有遗漏的相关细节可以解释为什么这行不通。事实上，如果有必要，您可以将此表构建为关系表，并拥有另一个如下所示的表：

    CREATE SET TABLE BigTable2 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT
         (
          TRANS_ID    VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
          OtherData1  VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
          OtherData2  SMALLINT NOT NULL,
          ....,
          OtherDataN  VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
   UNIQUE PRIMARY INDEX ( TRANS_ID );

希望有所帮助。

Answer 4

如果您不想重复编写相同的查询，我建议将其放在视图中。

为了提高性能，我建议如下：

• 按照 Rob Paller 的建议，将 LookupTable 上的主索引更改为 ENCODED_VAR。
• 在 LookupTable 中，添加一条新记录，其中 DECODED_DESC = null 且 ENCODED_VAR = 某个您永远不会使用的值。更新 BigTable 以用此值替换所有 null ENCODED_*。然后，您可以更改查询以使用所有内部联接并获得相同的结果。

Answer 5

你就不能这样吗：

SELECT 
TRANS_ID
, a.ENCODED_1
, CASE a.ENCODED_1 WHEN b.ENCODED THEN b.DECODED_DESC END DECODED_DESC_1
, a.ENCODED_2
, CASE a.ENCODED_2 WHEN b.ENCODED THEN b.DECODED_DESC END DECODED_DESC_2
...
, a.ENCODED_31
, CASE a.ENCODED_31 WHEN b.ENCODED THEN b.DECODED_DESC END DECODED_DESC_31
, a.ENCODED_32
, CASE a.ENCODED_32 WHEN b.ENCODED THEN b.DECODED_DESC END DECODED_DESC_32
FROM BigTable a
 LEFT JOIN LookupTable b ON (
   a.ENCODED_1 = b.ENCODED OR
   a.ENCODED_2 = b.ENCODED OR
   ...
   a.ENCODED_31 = b.ENCODED OR
   a.ENCODED_32 = b.ENCODED
 )

？

我也可能想重写加入条件如下：

...ON b.ENCODED IN (a.ENCODED_1, a.ENCODED_2, ... a.ENCODED_31, a.ENCODED_32)

但我不确定它是否不能比以前的版本慢。实际上，我的猜测是，它确实会变慢，但我仍然会验证这一点。

Answer 6

我也遇到了同样的问题，也是在 Teradata 上。一位同事告诉我一个优雅的解决方案，使用单个 LEFT OUTER JOIN 和多个 CASE 语句。

但是，您的示例有点令人困惑，因为您要加入的列不存在（“LookupTable”中的“ENCODED”列，我认为应该是“ENCODED_VAR”？）。

SELECT     TRANS_ID    
           , a.ENCODED_1    
           , MAX(CASE WHEN b.ENCODED_VAR = a.ENCODED_1
                 THEN b.DECODED_DESC
                 ELSE NULL
             END) DECODED_DESC_1    
           , a.ENCODED_2    
           , MAX(CASE WHEN b.ENCODED_VAR = a.ENCODED_2
                 THEN b.DECODED_DESC
                 ELSE NULL
             END) DECODED_DESC_2    
           ...    
           , a.ENCODED_31    
           , MAX(CASE WHEN b.ENCODED_VAR = a.ENCODED_31
                 THEN b.DECODED_DESC
                 ELSE NULL
             END) DECODED_DESC_31    
           , a.ENCODED_32    
           , MAX(CASE WHEN b.ENCODED_VAR = a.ENCODED_32
                 THEN b.DECODED_DESC
                 ELSE NULL
             END) DECODED_DESC_32    
FROM BigTable a
LEFT OUTER JOIN LookupTable b 
    ON a.ENCODED_1 = b.ENCODED_VAR
    OR a.ENCODED_2 = b.ENCODED_VAR
    ...
    OR a.ENCODED_31 = b.ENCODED_VAR
    OR a.ENCODED_32 = b.ENCODED_VAR
GROUP BY a.TRANS_ID 

这确实依赖于 BigTable 中的 ENCODED_n 和 LookupTable 中的 ENCODED_VAR 之间存在 1:1 关系。

另外，顺便说一句，您不应使用随机数作为 Teradata 表中的主索引。虽然这将为您提供良好的表分布，但在进行表查找时它将完全无用。如果您对 PI 使用通用联接字段，则数据库可以直接转到存储数据的 AMP。然而，如果没有这个，DBMS 必须每次都进行全表扫描。只要分布仍然合理，您就可以使用 ENCODED_VAR 作为 PRIMARY INDEX 并看到性能的显着提高。

我希望这能起作用。我认为这适用于你的情况。我尚未验证我的代码是否正确，但它与我自己的解决方案非常相似。