Teradata:别名的使用影响 EXPLAIN 时间估计 [英] Teradata: use of aliases impacts EXPLAIN estimation of time

问题描述

我有一个相对简单的查询

SELECT, db1.something, COALESCE(db2.something_else, 'NA') 作为 something2从 dwh.db_1 AS db1左连接 dwh.db_2 AS db2 ON db1.some_id = db2 = some_id

EXPLAIN 给出了超过 15 秒的估计时间.

另一方面，解释如下，我们基本上用表名替换了别名:

SELECT, db1.something, COALESCE(db_2.something_else, 'NA') 作为 something2从 dwh.db_1 AS db1左连接 dwh.db_2 AS db2 ON db1.some_id = db2.some_id

估计时间超过 4 小时，似乎系统正在尝试在某个线轴上执行产品连接(我无法真正遵循计划步骤的顺序).

我一直认为别名只是别名，对性能没有影响.

解决方案

估计的时间大概是正确的 :-)

表别名并不是真正的别名，它替换了该查询中的表名.在 Teradata 中使用原始表名不会导致错误消息(就像在大多数其他 DBMS 中一样)，但会导致交叉加入.

为什么?好吧，Teradata 是在标准 SQL 出现之前实现的，最初的查询语言称为 TEQUEL(TEradata QUEry Language)，其语法不需要在 FROM 中列出表.一个简单的RETRIEVE TableName.ColumnName 携带足够的信息供解析器/优化器解析表名和列名.没有关闭它的标志，一些客户端工具拒绝提交它，但您仍然可以在BTEQ中提交RETRIEVE.

在上面的例子中，你混合了旧的 TEQUEL 和 SQL，优化器有 3 个表，但只有一个连接条件，结果在与第三个表的 CROSS 连接中.

至少在解释中很容易发现.优化器将在最后一步执行这个愚蠢的连接，因此滚动到最后，您将看到 使用乘积连接进行连接，连接条件为 ("(1=1)").>

I have a relative simple query

SELECT
    , db1.something
    , COALESCE(db2.something_else, 'NA') AS something2
FROM dwh.db_1 AS db1
LEFT JOIN dwh.db_2 AS db2 ON db1.some_id = db2 = some_id

EXPLAIN gives an estimated time of something more than 15 seconds.

On the other hand, explain on the following, where we basically replaced the alias with the table name:

SELECT
    , db1.something
    , COALESCE(db_2.something_else, 'NA') AS something2
FROM dwh.db_1 AS db1
LEFT JOIN dwh.db_2 AS db2 ON db1.some_id = db2.some_id

gives an estimated time of over 4 hours, where it seems like the system is trying to execute a product join on some spool (I can't really follow the sequence of planning steps).

I always thought that aliases are just aliases and have no impact on perf.

解决方案

The estimated time is probably correct :-)

A Table-Alias is not really an alias, it replaces the tablename within that query. In Teradata using the original tablename doesn't result in an error message (as it does within most other DBMSes), but it causes a CROSS join.

Why? Well, Teradata was implemented before there was Standard SQL, the initial query language was called TEQUEL (TEradata QUEry Language), whose syntax didn't require to list tables within FROM. A simple RETRIEVE TableName.ColumnName carried enough information for the Parser/Optimizer to resolve tablename and columnname. There's no flag to switch it off, some client tools refuse to submit it, but you can still submit RETRIEVE in BTEQ.

Within that above example you're mixing old TEQUEL and SQL, there are 3 tables for the optimizer, but only one join-condition, this results in a CROSS join to the third table.

At least it's easy to spot in Explain. The optimizer will do this stupid join as last step, so scroll to the end and you will see joined using a product join, with a join condition of ("(1=1)").

这篇关于Teradata:别名的使用影响 EXPLAIN 时间估计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！