Archives of the TeradataForum
Message 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 full-table 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 re-run 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 full-table scan).
The release where this occurred: V2R4.0.1
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|