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
what is happening that multiple simple insert statements are so slow.
Simplistically, each statement will:
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.