2

Have a value list like`

`where a.c1 in ( list ) `

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.

user1874594
  • 2,277
  • 1
  • 25
  • 49

1 Answers1

3

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 
 )

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

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Thanks so much. I will give that a try and post a follow up in terms of before & after metrics – user1874594 Nov 04 '15 at 13:22
  • My list is a string list each string is 10 char. What I'd have done is just the spool table for an exclusion join `where EXISTS ( sel '1' from table where vt.x=fct.y ) `. I am not sure if this table created on the fly can be used inside it . I guess I'd have to inner join if after nominating dt as an extra table in the main query with conditon `fact.y=dt.token` ? – user1874594 Nov 04 '15 at 19:45
  • 2
    I just tried a `NOT EXISTS`/ EXISTS` and got a strange plans for both, only joins and `IN` look good. You talk about *exclusion join* which indicates `NOT EXISTS`, you might rewrite it using the `LEFT JOIN ... WHERE token IS NULL` approach – dnoeth Nov 04 '15 at 23:08
  • Just on a follow up - the impact CPU came down from 175K to 5K. thanks a bunch again – user1874594 Nov 09 '15 at 18:43