SQL 中的 Teradata Optimizer Equal vs Like [英] Teradata Optimizer Equal vs Like in SQL
问题描述
我目前正在尝试优化一些 bobj 报告，其中我们的后端是 Teradata.Teradata 优化器 似乎非常挑剔，我想知道是否有人提出了解决方案或变通方法来让优化器以类似的方式处理喜欢等于.
I am currently trying to optimize some bobj reports where our backend is Teradata. The Teradata optimizer seems very finicky and I was wondering if anyone has come up with a solution or a workaround to get the optimizer to treat likes in a similar regard to equals.
My issue is that we allow the user to input one of two methods:
1. Enter the Number:
or
2. Enter a Number like:
选项一表现得像梦一样，而选项二将我们的查询时间从 6 秒拖到 2 分钟.
Option one performs like a dream while option two is dragging our query times from 6 seconds to 2 minutes.
除此之外；有没有人知道关于为 teradata 优化器优化 SQL 语句的任何好文章、讨论、视频等?
In addition to this; does anyone know of any good articles, discussions, vidoes, etc.. on optimizing SQL statements for the teradata optimizer?
推荐答案
因为该列被定义为 VARCHAR 并且您正在使用 LIKE 运算符，所以您消除了使用 PI 进行单个 AMP 访问的可能性.请记住，主索引的首要任务是在系统中的 AMP 之间分发数据.因为您对 PI 使用 LIKE 运算符，所以优化器必须执行所有 AMP"操作以满足 LIKE 运算符.
Because the column is defined as a VARCHAR and you are using the LIKE operator you eliminate the possibility of using the PI for single AMP access. Remember, the primary indexes first job is distributing the data across the AMPs in the system. Because you are using the LIKE operator against the PI the optimizer must perform an 'all AMP' operation to satisfy the LIKE operator.
WHERE MyPIColumn LIKE '123%'
以 123 开头的值的散列可以并且最终会出现在多个 AMP 上.
The hashing of values starting with 123 can and will end up on multiple AMPs.
WHERE MyPIColum = '123'
123 的散列会将每条记录放在同一个 AMP 上.查询123"将始终是单个 AMP 操作.
The hashing of 123 will place every single record on the same AMP. Querying for '123' will always be a single AMP operation.
这方面的统计数据可能有助于行估计，但可能不会消除所有 AMP"操作.
Statistics on this may help with row estimates but will likely not eliminate the 'all AMP' operation.
- 这是唯一 PI 还是非唯一 PI?
- 为什么将数据类型选择为字符而非数字?尽管 GT(E) 或 LT(E) 可能会导致相同的全 AMP"操作.
- 此 PI 是否由其他表共享便于 AMP 本地化的模型加入策略?
这篇关于SQL 中的 Teradata Optimizer Equal vs Like的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
