Inserting values in Oracle - different methods

I read about different methods how to insert values in oracle database, like insert, insert all, CTAS, insert into select, SQL Loader but I would like to understand what is the reason that one is faster than the other.

for example if i have 10000 values.
using the simplest method which is the worst one

 insert into table_1 values ( 1) ; insert into table_1 values (2);....insert into table_1 values (10000);

this is very slow but my question what is happening compared to INSERT INTO SELECT or CTAS

Of course CTAS create a table first but as insert into select it must enter the value in a "table" which is a block and save it into the file. Suppose the table is empty (no table at all in case of CTAS) , what is happening that multiple simple insert statements are so slow.

physically it must do the same thing but probably each method has different overheads

a. simple insert statements but multiple times
b. insert into select
c. insert all statement
d. SQL LOADER
e. CTAS

except the last method suppose the table is

1. empty
2. sparse - some rows has been deleted before the insert

> what is happening that multiple simple insert statements are so slow.

Simplistically, each statement will:

• Be parsed by the SQL engine;
• Write to the log files (i.e. archive log, undo/redo log); and
• Get written to data file.

When you use multiple INSERT statements then you have multiple parses, multiple writes to the log files and (maybe) multiple writes to the data file.

However, when you have a single INSERT ALL ... or INSERT ... SELECT ... or CREATE TABLE ... AS SELECT ... then there only needs to be a single (possibly larger) parse and a single (larger) write to the log file and data file. This means that you can reduce the I/O overheads significantly and if the statements are being run over a network then you will reduce transmission overheads as well.

CREATE TABLE ... AS SELECT ... is a DDL statement and will implicitly COMMIT both before and after the statement. Therefore, it is difficult to compare to the other statements as it is not doing exactly the same thing.

As mentioned by @astentx, SQL Loader uses a direct-path insert which bypasses the buffer cache and writes directly to the data files; however, under certain circumstances you can also use direct-path insert with INSERT statements.

It would probably be impossible to say which method is the "best" but you should find that multiple INSERT statements has far greater overheads that any of the other options and would be the slowest option.

1. INSERT INTO tbl VALUES works on only a single row (unless using an bulk bind such as with OCI SYS_DL_CURSOR or FORALL INSERT VALUES or SQL*Loader direct path) and as such you have an excessive amount of overhead for parse, execution, buffer concurrency mechanisms, redo & undo entries, index maintenance, and network round trip relative to the amount of work accomplished. It's quick with one row, but miserable with a million rows.

2. INSERT INTO tbl SELECT works on a row source, as many rows as you'd like all at once. Therefore you don't have the network round trip, parsing and execution overhead for every row.

3. INSERT /*+ APPEND */ INTO tbl SELECT improves things further by using direct path (bypasses the buffer cache/DBWR and writes directly to disk) and also formats completely new blocks above the high-water mark (HWM) rather than finding empty space in existing blocks. This also means that it doesn't need to store undo for the data itself, as a rollback can simply discard the newly allocated blocks and so doesn't need undo information for each row. If logging is not being forced and you add NOLOGGING to the hints or the table, this kind of insert will also bypass redo. This really speeds things up (but makes it unrecoverable).

4. INSERT INTO tbl SELECT /*+ PARALLEL(x) */ ... speeds things up by throwing multiple parallel execution slaves at the SELECT portion of the query, which helps only if you are doing table joins or full scan with a filter predicate... it won't help with a straight SELECT * FROM table because the parallel slaves will be bottlenecked on the serial insert load step.

5. INSERT /*+ ENABLE_PARALLEL_DML APPEND PARALLEL(x) */ INTO tbl SELECT further speeds things up by parallelizing the load step itself, the actual block formatting and loading. It also postpones index maintenance until after the load which further speeds things up if you have indexes.

6. CREATE TABLE tbl AS SELECT (CTAS) with PARALLEL (DEGREE x) (and especially with NOLOGGING) is the fastest of all because it gets the benefit of all of the above plus there's no need for undo, locking, zero index maintenance, data segment header lookups for checking space bitmaps, etc.. it's working on its own brand new segment so none of these are necessary. I have repeatedly found that CTAS outperforms parallel append insert of an existing, empty table, though if you set up your insert carefully as outlined above, you can get close to CTAS performance.