Run Automatic Commit

[DBA_LUCAS]

Environment information:
* Oracle version: 11g
* Operating system: Linux

Dear friends,

I am with A problem I need to solve, I need to run a query with many lines, but I need to be done a commit for so many lines.
Example:


I will perform an insert from a SELECT, let's say this select will return 15000 lines that will be inserted, but I want 3000 in 3000 rows run a Commit.

Some idea how I do it?

[dr_gori]

One way is to do a normal cursor that inserts line per line. Hence you count and make fun of so many in so many. This is the worst way.

Another way is to create a cursor that makes several inserts. But each insert makes 3000 lines. Something + or - so:
* makes a loop
* makes a

Select all

 INSERT .... WHERE COD >=1 and COD <=3000;

* Commit.

Then the loop increments the value of the codes. The next insert will be 3001 to 6000. And so on.

Another way is also using Bulk Insert. But Tom Kyte already said, "If you can do a 'direct insert', it's always faster." Read about Bulk Insert!

[DBA_LUCAS]

First, thank you!

Unfortunately, this way could not be done because my command and
an insert into select that is recorded in a table to be applied
later.

That is, I would need that in the insert already had something that implies that
each x records inserted, an automatic commit would be given.

I have not read about Bulk Insert yet, but I will do it !!

Do you have any other idea?

[dr_gori]

Well, if you have only 1 insert, I believe it is not possible that "automatic" form. Maybe if you add on the WHERE as in the example above and try to make this insert in smaller batches and to be committing.

[DBA_LUCAS]

Yes this is the idea, I have already seen once that at the end of the insert has something like the thick mode "WHERE Commit X on x lines", but I do not remember the writing and how it works it.
I wanted in case of inserting 15,000 lines, being able to set any option to be able to commit from 5000 in 5000 for example ...

[DBA_LUCAS]

In fact it will insert everything at once, but in case it is a 1 million insertion of records, you can burst the rollback segment, so I wanted to know if you have any form of the Oracle to go and commit internally, but in the interaction with the user The insert will be done at once so !!!

[diegopedrao]

The only way would be the cursors there are n jewelry to do ...

The problem of you start from x in x records is this, suppose next scenario:

] - You need to give 10% salary increase for people who earn up to R $ 1,000 and 5% increase for people who earn more than R $ 1,000;
- You are running the cursor by 10,000 records there is joãozinho who earns R $ 500,00 ai you gave an increase of 10% his salary was for R $ 550,00, ai for a motive X The connection with the seat fell. You will have to rotate the process again.
- Turning the process again Joãozinho still earns less than R $ 1000 being so he will receive 2 increases.

This was only an explanation in the case of the update command, as depending on which use of the system the unico way is the insert / update in 1 single block.

ATT,

Diego Monteiro

[DBA_LUCAS]

I will try to explain better what I am wanting:

I will have two distinct banks x and y, run an insert in a table in the x bank, but this insert is through a select in the bank Y.

Ex:

Select all

Insert into TABELAX (COD,NOME,CPF) 
values (SELECT COD,NOME,CPF from TABELAY)

So I will not do this through procedure, so I can not have loop, my insert stored in a table in bench y in a varchar type field (4000) or text ...

Oh I will trigger this table so that it runs the INSERT in the table by selecting a SELECT in the table , taking into consideration that we are in distinct banks.
So that as soon as I trigger the table that will make the insertion this insert can be 1 million records, and not to burst my rollback segment needed the Oracle commissioned internally x in x records ...

What I saw once was something like this example below, but I do not remember how the Clause Were placed by the DBA that showed me this at the time:

Ex:

Select all

Insert into TABELAX (COD,NOME,CPF) 
values (SELECT COD,NOME,CPF from TABELAY) 
where commit NNN and NNN ;

[DBA_LUCAS]

I believe that it is not the Were Clause, but she used a clause after the insert of the type I expressed above:

Select all

Insert into TABELAX (COD,NOME,CPF)  
values (SELECT COD,NOME,CPF from TABELAY)  
where commit NNN and NNN ; 

Some clause that will commit x in x}