Archives of the TeradataForum

Message Posted: Mon, 28 Jan 2008 @ 17:03:01 GMT


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

Subj:		Re: Sorting of a Select

From:		Mutsch, Joerg

Christopher Stephens wrote:

For all of our benefit, can you tell us which frontend tool refuses to generate an ORDER BY clause when querying Teradata tables or views? Is it custom built, or is it something we may have heard of / may need to work with in the future?

Also, do you know of any database platforms that do allow views to be created with an ORDER BY clause? Does SQL Server, Oracle, DB2, etc. allow this or something?

Thanks Chris.

I am using Cognos 8 as the frontend. The issue here lies in Analysis Studio where you can navigate in the dimensional date (e.g. Year -> Month -> Day ).

The dimensions will be defined in the Framework Manager. You can define an order by when you define which table you want to access, and which columns you want to include

     SELECT * FROM RD_VIEWS.THE_WEEK_V
     ORDER BY WEEKID

But when you try to read some test data you can see that the order by will be cut off. Only the SQL like

     SELECT
         "THE_WEEK_V"."WeekID"      "WeekID" ,
         "THE_WEEK_V"."WeekCD"    "WeekCD" ,
         "THE_WEEK_V"."WeekDesc" "WeekDesc"
     FROM "RD_VIEWS"."THE_WEEK_V" "THE_WEEK_V"

Will be send out to the DB.

As written when I do not use the Alias Names this works perfectly. As soon as I use the SQL as above the sort will be made based on the Description and not on the ID. I checked the Explain. Without the explain look like this (quite straight forward):

     EXPLAIN select
     "THE_WEEK_V" ."WeekID"  ,
     "THE_WEEK_V"."WeekCD"  ,
     "THE_WEEK_V"."WeekDesc"
     from RD_VIEWS.THE_WEEK_V

`Explanation --------------------------------------------------`
	`1)`	`First, we lock RD.THE_WEEK_V for access.`
	`2)`	`Next, we do an all-AMPs RETRIEVE step from RD.THE_WEEK_V by way of an all-rows scan with no residual conditions into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 104 rows. The estimated time for this step is 0.03 seconds.`
	`3)`	`Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.`
	`->`	`The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.03 seconds.`

When I am using the alias (as Cognos does) the Explain is telling me about a regroup thus a new sort:

     EXPLAIN SELECT
        "THE_WEEK_V"."WeekID"   "WeekID" ,
        "THE_WEEK_V"."WeekCD" 	"WeekCD" ,
            "THE_WEEK_V"."WeekDesc" "WeekDesc"
     FROM "RD_VIEWS"."THE_WEEK_V" "THE_WEEK_V"

`Explanation --------------------------------------------------`
	`1)`	`First, we lock RD.THE_WEEK_V for access.`
	`2)`	`Next, we do an all-AMPs STAT FUNCTION step from RD.THE_WEEK_V by way of an all-rows scan with no residual conditions into Spool 5 (Last Use), which is assumed to be redistributed by value to all AMPs. The result rows are put into Spool 3 (all_amps), which is built locally on the AMPs.`
	`3)`	`We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan into Spool 1 (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 104 rows. The estimated time for this step is 0.04 seconds.`
	`4)`	`We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan into Spool 8 (group_amps), which is built locally on the AMPs. The size of Spool 8 is estimated with no confidence to be 104 rows. The estimated time for this step is 0.04 seconds.`
	`5)`	`Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.`
	`->`	`The contents of Spool 8 are sent back to the user as the result of statement 1.`

Oracle, DB2 and others provides the ability to create an Order by already within the view, which Teradata does not not since it is not ANSI conform.

Thanks

Joerg


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

Attachments

Library

Quick Reference