在不使用易失性表的情况下优化Teradata中的巨大价值列表 [英] Optimizing huge value list in Teradata without volatile tables

问题描述

具有类似`

`where a.c1 in ( list ) `

然后将列表推入volatile表是最好的出路.但是，这是通过cognos&IBM不够聪明，无法知道Teradata的易失性表是什么.我希望是这样，所以我可以使用排除逻辑Exists来浏览易失性表的内容.所以没有volatile表，我有一个值列表(list)中的a.c1 它具有类似5K的值.将该列表保留在报告中被证明是昂贵的.我想知道是否可以将这种列表存储在报表中之前的某个位置.CTE如何使用和存在于CTE上，将获得类似的收益.

Then shoving the list in the volatile table is the best way out. However this is being done via cognos & IBM isn't smart enough to know what Teradata's volatile table is. I wish It was so I could use exclusion logic Exists to go through the volatile table contents. So without volatile table , I have a value list where a.c1 in ( list ) which has like 5K values. Keeping that list in the report is proving expensive. I wondered if it was possible to store this kind of list some place before bringing it in the report. How about CTE and using exists on a CTE , would that achieve similar gains.

推荐答案

您可以将列表作为字符串传递，然后将其拆分为表格，例如获取整数列表:

You can pass the list as a string and then split it into a table, e.g. for a list of integers:

where a.c1 in
 (
   SELECT CAST(token AS INT)
   FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, '1,2,3,4,5,6,7,8,9,5000', ',')
        RETURNS (outkey INTEGER,
                 tokennum INTEGER,
                 token VARCHAR(10) CHARACTER SET UNICODE)
              ) AS dt 
 )

当然优化器不知道返回的行数，所以最好检查一下Explain ...

Of course the optimizer has no knowledge about the number of rows returned, so better check Explain...

这篇关于在不使用易失性表的情况下优化Teradata中的巨大价值列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！