Archives of the TeradataForum

Message Posted: Fri, 04 Sep 2009 @ 19:13:10 GMT

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

Shri -

I can't see how the order of data in a table ever matters. I can see how the order of how you access it matters, but that's what order by in SQL does. How does the order in the table itself make a difference? You said yourself that the order in the table doesn't matter: "based on the order by output we do further actions." Order by output means put an order by into whatever's selecting from that table, it doesn't mean the order in the table.

The only context I can see needing to sort the insert is if there's a trigger on the target table and it must be executed in a specific order, but I'd hate to think that anybody would actually implement something like that.

If your application depends on the order of rows in a table and it's running on Oracle, you're playing a dangerous game (a game you can't even play in Teradata). The only time ordering is guaranteed in either system is query output that includes an order by clause. Any other ordering you see is coincidental and may or may not be repeatable. In neither system can you do something like

     Insert  into x select  from y order by a

and expect

     Select  from x

to be in any sort of order. It may be in order by a, and maybe so far you've always seen it come back in order by a, but that's coincidence. If you're relying on this, you're making a mistake. The only way to guarantee a particular order in a particular circumstance is by putting it in the sql. (True for Oracle and Teradata; don't know enough about DB2, but it should be true since it's one of the basics of RDBMS.)

This seems like a similar thing to thinking that GROUP BY in query means that results will be sorted.

If the order really matters, don't order the insert, order the select from the inserted table. If you don't want to insert the ordering field, insert a ranking field:

     Insert into table2 (x, y, z, order_field)
     Select x, y, z row_number() over (order by z) from table1

Then your app can

     Select x, y, z from table2 order by order_field.

I cannot stress enough that 1) you should treat the order of physical rows in a table as irrelevant; 2) if you need to access rows in a particular order, it must be done in the SQL that's selecting them; 3) the order of physical rows of a table in Tereadata cannot be controlled (but it doesn't matter - see 1).

Dave C

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