Teradata 优化器 SQL 中的 Equal 与 Like
我目前正在尝试优化一些 bobj 报告，其中我们的后端是 Teradata。 Teradata 优化器 似乎非常挑剔，我想知道是否有人提出了解决方案或解决方法 来让优化器以类似的方式处理点赞等于。
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 分钟。
除此之外;有谁知道关于为 teradata 优化器优化 SQL 语句的任何好的文章、讨论、视频等吗？
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:
Option one performs like a dream while option two is dragging our query times from 6 seconds to 2 minutes.
In addition to this; does anyone know of any good articles, discussions, vidoes, etc.. on optimizing SQL statements for the teradata optimizer?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如果您正在进行直接 VARCHAR 比较，即
您可以尝试在该列上使用 NUSI。确保收集表的主索引和索引的统计信息
If you are doing a direct VARCHAR comparison, ie
then you could try to use a NUSI on that column. Make sure that you collect statistics for the table's primary index and for the index
由于该列被定义为 VARCHAR 并且您正在使用 LIKE 运算符，因此您消除了使用 PI 进行单 AMP 访问的可能性。请记住，主索引的首要任务是在系统中的 AMP 之间分配数据。由于您对 PI 使用 LIKE 运算符，因此优化器必须执行“全 AMP”操作以满足 LIKE 运算符的要求。
以 123 开头的值的散列可以并且最终会出现在多个 AMP 上。
123 的散列会将每条记录放置在同一个 AMP 上。查询“123”将始终是单个 AMP 操作。
有关此的统计数据可能有助于行估计，但可能不会消除“所有 AMP”操作。
该模型促进 AMP 本地化
加盟策略？
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.
The hashing of values starting with 123 can and will end up on multiple AMPs.
The hashing of 123 will place every single record on the same AMP. Querying for '123' will always be a single AMP operation.
Statistics on this may help with row estimates but will likely not eliminate the 'all AMP' operation.
the model to facilitate AMP local
join strategies?
我认为 Number 已被索引？ Teradata 使用哈希进行索引，因此 equals 将导致使用索引，而 like 将导致全表扫描。
如果您确实需要使用 like，那么您无能为力。您可以尝试的一件事是使用
Substr(Number, 1, 3) = '123'而不是Number LIKE '123%'。过去我已经从中获得了一些小的性能改进，但不要指望有什么惊人的效果。I'd take it that Number is indexed? Teradata uses hashing for indexing, so equals will result in the index being used, while like will result in a full table scan.
If you have a genuine need for using like, there's not an awful lot you can do. One thing you could try is using
Substr(Number, 1, 3) = '123'rather thanNumber LIKE '123%'. I've gotten small performance improvements from this in the past, but don't expect anything spectacular.您将需要一个全文索引/预标记化索引，例如 lucene，以及两个解析搜索。
例如，当将“12345”插入数据库时，创建从“1”、“12”、“123”、“234”...等到“12345”的链接。
然后，当使用find类似“123**”时，从查找表中找到“123”并查找记录“12345”
You will need a fulltext index / pre-tokenized index, e.g. lucene, and also a two parse search.
e.g. When insert a "12345" to your database, create link from "1", "12", "123", "234"...etc to "12345".
Then, when use find something like "123**", find "123" from the lookup table and the seek to the record "12345"