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.
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.
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?