Archives of the TeradataForum
Message Posted: Thu, 18 Jul 2002 @ 16:52:15 GMT
I've seen this problem at Boeing, and it's really frustrating to have gone to a lot of trouble selecting an index only to have it ignored.
Since were on the topic of IN lists, here's another incident for people to look at: RECAL8KZE.
We have a user that is generating queries with IN lists contining >2500 literals. Once you get past the parser time, the execution is very, very slow (especially if the literal list contains character data). If you take the literal values from the list and insert them into a table, then do an explicit join, the query runs more than 100X faster.
CREATE VOLATILE TABLE T2 AS (C1 )
This is not really a viable method for the users (even using global temporary tables) because there's no fast way to insert all the literal values into the temporary table. NCR could probably teach the parser & optimizer to convert a long list to a spool file and then do the joins, but that's a pretty significant change.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|