UDA

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-17-2007
09:42 PM

12-17-2007
09:42 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-18-2007
01:57 AM

12-18-2007
01:57 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-18-2007
02:04 AM

12-18-2007
02:04 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-18-2007
02:06 AM

12-18-2007
02:06 AM

Sorry for the typo...

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-18-2007
10:16 AM

12-18-2007
10:16 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-07-2011
12:27 PM

04-07-2011
12:27 PM

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

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

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.