FAST PATH INSERT SELECT

UDA

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
5 REPLIES

Re: FAST PATH INSERT SELECT

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

Re: FAST PATH INSERT SELECT

INSERT INTO Promotion
SELECT Name, DeptNo, YrsExp
FROM 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.

Re: FAST PATH INSERT SELECT

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

N/A

Re: FAST PATH INSERT SELECT

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

Re: FAST PATH INSERT SELECT

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