Archives of the TeradataForum

Message Posted: Thu, 11 Oct 2001 @ 10:05:47 GMT

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

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

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