All Forums UDA
dbread007 3 posts Joined 03/07
17 Dec 2007
FAST PATH INSERT SELECT

Can someone please explain the FAST PATH INSERT SELECT Teradata Function?I have 300,000 rows of data I need to insert into a table and then run queries on. With normal INSERT this takes hours.I know there is a function to speed this process up but DO NOT know how to use it.MANY THANKS,dbread007

Balamurugan B 81 posts Joined 09/07
18 Dec 2007

Hi,Try using teradata utilities like FastLoad/MultiLoad for faster insert operation.For more details on FastLoad and MultiLoad you can refer specific Teradata utility PDFs.Regards,Balamurugan

Regards,
Balamurugan

18 Dec 2007

INSERT INTO PromotionSELECT Name, DeptNo, YrsExpFROM employee WHERE YrsExp > 10;When the primary indexes (or indices) of promotion and employee tables are the same column then FAST PATH INSERT SELECT takes place and it is extremely fast. All Amps work parallelly because the source and target tables happen to be on the same path.

Ramakrishna Vedantam, Tata consultancy services, Hyderabad

18 Dec 2007

Sorry for the typo...All Amps work parallelly because the source and target tables happen to be on the same Amp..

Ramakrishna Vedantam, Tata consultancy services, Hyderabad

dnoeth 4628 posts Joined 11/04
18 Dec 2007

If the target table is emtpy at the begin of the transaction, then that Insert/Select doesn't need a Transient Journal, because in case of a Rollback it's just emptied again.This is called a "Fast Path Insert/Select".If PIs of target and source table match, it's faster, because for that AMP-local operation there's no redistribution necessary.But this is true for any kind of operation, not only "Fast Path Insert/Select".The OP didn't talk about the operation: "I have 300,000 rows of data I need to insert into a table and then run queries on. With normal INSERT this takes hours."This might be an Insert/Select into a Set table with lots of duplicate row checks or lots of single row Inserts using a tool.Solution for #1 would be a better PI and/or Multiset.Solution for #2 would be FastLoad or BTEQ/JDBC using arrays.Dieter

Dieter

jainayush007 50 posts Joined 03/11
07 Apr 2011

Hello,

My target table is empty. I have 8 tables to load data into this table. The PI of the target table isnt the same as that in the 8 table and neither the 8 tables have it among them. However, the datatype and size of all these PIs is the same across all the tables(8 source & target tables). Also, it would not be a simple select on each of the 8 tables. Each select would have a few other tables as well apart from the source table. Can I establish a fast path INSERT/SELECT in this scenario?

Regards,
Ayush Jain

You must sign in to leave a comment.