Archives of the TeradataForum

Message Posted: Thu, 18 Jul 2002 @ 16:52:15 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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.

SELECT FROM T1 WHERE C1 IN ('L1', 'L2', ... , 'L2500');

versus

CREATE VOLATILE TABLE T2 AS (C1 )
UNIQUE PRIMARY INDEX (C1) ON COMMIT PRESERVE ROWS;
INSERT INTO T2 VALUES ('L1');
INSERT INTO T2 VALUES ('L2');
...
INSERT INTP T2 VALUES ('L2500');
SELECT FROM T1 INNER JOIN T2 ON T1.C1 = T2.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.

/dave hough

	<Prev	Next>		<<First	<Prev	Next>	Last>>