
Archives of the TeradataForumMessage Posted: Thu, 11 Oct 2001 @ 10:05:47 GMT
Here's an interesting problem involving the RANDOM() function (it's been reported to NCR). If you're using the RANDOM() function to generate a primary index, you'll want to read on. Here's the situation: There's a table defined, for example, as CREATE SET TABLE RANTEST ( RAND_PI DECIMAL(15) , TEXTDATA CHAR(3000) ) PRIMARY INDEX (RAND_PI); First insert a single row with the following statement: INSERT INTO RANTEST VALUES (RANDOM(1,100000)); Let's say that RANDOM() returned a value of 1123. If we execute the statement: SEL RAND_PI FROM RANTEST WHERE RAND_PI > 0; We get the row with a PI of 1123 (you might note that the WHERE clause forced a fulltable scan: RAND_PI > 0). If we execute the statement: SEL RAND_PI FROM RANTEST WHERE RAND_PI = 1123; We get no rows found! The row is in the table, you just can't retrieve it by the PI. Instead of the INSERT above, if we used: INSERT INTO RANTEST SEL RANDOM(1,100000); Then if you rerun the SELECTs, then both work correctly  you can now retrieve the row by the primary index. The problem is that using RANDOM() with the VALUES clause causes the row to be hashed wrong and therefore stored on the wrong AMP (at that point, you can only find the row with a fulltable scan). The release where this occurred: V2R4.0.1
 
 
Copyright 2016  All Rights Reserved  
Last Modified: 28 Jun 2020  