All Forums Database
Bob18 3 posts Joined 03/15
26 Mar 2015
Order by Statement for Volatile Tables and Efficiency

Hi,
At my workplace I can't create permanent tables, so I have been creating volatible tables as middle steps for joins later on very large data set. 
I would like to order the dataset by group name and effective date, but the order option isn't allowed for volatile table (FYI, I have version 14.10). Is there a way I can sort the data so that I can use it for faster join and easier data manipulation? 
Below is a sample code. Please let me know if I can sort the table through another method or if there is a more efficient method.

create volatile table MbrTbl  as 

 

( select   group, subgroup, eff_date, mbrCount

from memberTBL

 

group by 1,2,3,

 

) with data PRIMARY INDEX (group, subgroup, mbrCount) ON COMMIT PRESERVE ROWS;

COLLECT STATS ON NH_15_grp INDEX (group, subgroup, mbrCount);

 

Thanks,

 

Bob

ravimans 54 posts Joined 02/14
26 Mar 2015

Hi Bob,

 

I am not clear. What's the issue in the above code and what error message you are getting while creating volatile table?

Only issue i can see in the above code is extra comma in the group by 1,2,3. Other than that it's working fine. 

 

Qn: I would like to order the dataset by group name and effective date, but the order option isn't allowed for volatile 

table (FYI, I have version 14.10)

-- I think you can order the dataset in the select statement. What other ways you are trying?

Rmsranjith 4 posts Joined 03/09
26 Mar 2015

First of all having an order by won't help in faster joins. Teradata stores the table completely in a different order based on hashing on primary index. If you would like to see performance, consider using group and eff date as primary index.
 

You must sign in to leave a comment.