CREATE TABLE AS SELECT (CTAS)

The absolute fastest way to get data into a table is to create the table from scratch using CREATE TABLE AS SELECT (CTAS). eg.

CREATE TABLE my_table
TABLESPACE t10
AS
SELECT *
FROM   my_other_table

The best thing about CTAS is that it does not use rollback segments. If your SELECT clause performs a sort (ie. in a GROUP BY, UNION, sort-merge join, etc) then your query may be limited by the database sort-area size. Similarly, if it performs a hash join then you may be limited by the database hash-area size. Otherwise you are only limited in the number of rows you create by the size of the tablespace into which the table will go.

The UNRECOVERABLE clause may be used to make the CREATE even faster, however in the event of crash and recovery from backup made prior to the CREATE, the new table will need to be recreated.

If the table you want to insert into already exists (and you want to keep the rows already in it) then Direct Load Insert may be a better option.

CTAS and Exchange Partition

CTAS is a particularly powerful tool for populating or rebuilding a single partition of a partitioned table. Say we have a table that contains monthly aggregates of a base table, and is partitioned monthly. We could run a monthly job to CREATE TABLE AS SELECT the data for just one month, and then use ALTER TABLE EXCHANGE PARTITION to swap the new table into the partitioned monthly aggregate table.