Archives of the TeradataForum

Message Posted: Thu, 06 Jan 2005 @ 13:36:56 GMT

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

Ravindar,

If you sort by only that one column (A), which has duplicate values then the order in which those duplicate values are displayed will be the order determined by the optimizer (i.e. most efficient - time wise). You see, Teradata has a mature optimizer that will determine the least expensive way (in terms of time) to return the results of any given transaction.

Saying this, when you execute a select statement, after the parsing engine verifies your syntax and semantics, the optimizer will calculate the most efficient way to obtain the data requested and will create some executable steps to be performed by the AMPs. (Note: these steps dictate, the order in which the rows are pulled). The acquired rows are then placed in spool and this is where your sort is executed based on the ORDER by field that you specified. If there are duplicate rows in the column that is specified for the order by field then those rows will be ordered in the same way the results were received originally (given that you only specified one sort column). If you specify multiple columns for the sort fields then the data pulled will be sorted accordingly (while in spool) and any remaining columns (i.e. not sort columns) that may have duplicate values will be treated as specified before.

To can verify this by doing a SELECT on the table in question without sorting and note the order in which the rows are displayed (especially rows with duplicate values in the "intended" sort column)

Then do another select (same as before) on the table again, this time order by the column you intended to sort on before (i.e. one that has duplicate values). The order of the sort-column's duplicate values in the sorted result should be the same as it was in the unsorted result, just sorted. If you are sorting on multiple columns, then of course this will be harder to track but you can do an explain on the query to see what the execution plan is.

Yeah, it's a little verbose.

Hope it helps.

Sherian M.

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