1
Hello, good morning!
I have some questions about how best to include mass data in an Oracle database so that the database does not suffer so much. Doing some research I identified two methods to do this insertion and wanted to understand which is the most performatic method to effect the inclusion of lots of data in the Oracle database and understand the difference between the two methods and if there is any more:
The first case I saw was through select dual:
insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual
I couldn’t find much about his operation and I wanted to understand better. The second is the Insert all:
INSERT ALL
INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;
That from what I understand it’s like he’s doing an Internet at a time.
Which one is more performatic?
Run your query and see the time of each, which take less time to process will be more performative.
– Ivan Ferrer
You talk about different things , entering test base data is something that is done sporadically and then the secondary performance , better to focus on the quality of the data, I would make a plsql block generating the data sequentially or aleátoria. https://stackoverflow.com/questions/33860428/insert-random-data-in-oracle-table
– Motta
In this situation that I am doing I will insert data in a production database at the time of insertion of another process that runs in parallel with mine (The other process includes more than 10Gb data), would be about a thousand to 2 thousand lines per minute, out updates on another table. I talked to the team’s DBA they do these operations using INSERT ALL only that the database already suffers a lot today. I wanted to understand if you knew the difference in performance between operations or if it makes no difference to the Oracle bank?
– Ismael SIlva
I followed the advice of Ivan Ferrer and tested with a thousand lines each operation, INSERT with dual, INSERT ALL with a sequence Trigger (I was unable to run INSERT ALL with GENERATED BY DEFAULT ON NULL AS IDENTITY in Primary Key) and an Insert one by one. the difference was extremely large: 1,000 Rows affected in 618 ms -> With Dual, 1,000 Rows affected in 1 s 137 ms -> One by one in Begin, 1,000 Rows affected in 3 s 166 ms -> Insert all.
– Ismael SIlva
This depends a lot on how your Oracle is set up, where it’s running, which version. It’s so much involved... It’s not just execution plan no. Let me give you an example: If you run this same command on two different machines (with identical database), one with Windows and the other with Redhat will have TOTALLY different and distinct results. Then puts more details of your environment (at least version of Oracle and Operating System).
– Mateus