Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 07 Sep 2009 @ 14:37:10 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: INSERT into table with select Order by clause
From:   John Hall

Rather bluntly, you're wasting your time. Even worst, you're ignoring good advice.

Internally, the Teradata does not keep data in a ordered fashion. The data is distributed across the AMPs based on the hash code of the primary index. The hash code does not imply any type of order, it's just a method for distributing the data.

The first record of your data may go to AMP #3, the second record to AMP #1091 and the third record to AMP #1. There is no internal order to a table.

Go ahead and conduct an experiment: Load your sorted data into a table - however you choose to do it. Then perform a simple:

     SEL * FROM db.table;

Run it several times and compare the results. You'll see that the rows are probably returned in a relatively random fashion.

The answer set will not be ordered until you put the ORDER BY on the exporting SELECT:

     SEL * FROM db.table ORDER BY 1,2;

The one thing that occurs to me is that if you need your data to match the order in which it's loaded, then you'll need to add a column to save (or preserve) that order (for example, a unique record number).

  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020