I create a table by following sql syntax:
The FA_Test1 has 50 millions rows, and the expected row number of FA_Test2 is 4 millions.
If I put the above sql syntax in TD Studion "SQL editor" to create table, the consuming time is about 2 minutes.
But if I put sql syntax in FastLoad script and execute by "fastload < script_name" command, the consuming time is exceed 2 hours, my script is:
The TD Studio Client and Fastload command client are in the same machine. Does anybody know what I miss?
To be honest, I'm surprised that your Fastload even ran.
Fastload is intended to load data from a client system to Teradata.
The 'create table as' command does not load data from a client system. It simply transfers data from one/more tables to a target table.
I don't know why Fastload is so much slower, but why would you even consider Fastload for this operation?
please consider to put an explicit primary index on your create table statement otherwise it defaults to the first column (and your table could be way skewed).
CREATE TABLE FA_Test2 AS (SELECT * FROM FA_Test1 WHERE CYCLE_ID = '201611') WITH DATA PRIMARY IDEX (yourchoice);
Have a nice day.
Below syntax is being used for creating a mulitiset table with billion rows
Create multiset table a (select * from b ) with data and statistics primary index (a1);
If we do not use statistics does that make any difference ?
The 'with statistics' will not make any difference to the performance of the 'create table as' command.