Order by Statement for Volatile Tables and Efficiency

Database
Fan

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

2 REPLIES
Enthusiast

Re: Order by Statement for Volatile Tables and Efficiency

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?

Re: Order by Statement for Volatile Tables and Efficiency

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.