Difference in performance of pl / sql forall insert query and simple SQL insert

Question

We used a temporary table to store intermediate results in the pl / sql stored procedure. Can anyone tell if there is a performance difference between the insert insert build through pl / sql and the plain SQL insert.

Paste in [Table Name] [Select query Return a huge amount of data]

or

Cursor for [Select query returning a huge amount of data]

open cursor

sampling a volume collector into a collection

Use FORALL to insert

Which of the above options is better to insert a huge amount of temporary data ?.

Answer 1

Some experimental data for your problem (Oracle 9.2)

bulk collection

DECLARE TYPE t_number_table IS TABLE OF NUMBER; v_tab t_number_table; BEGIN SELECT ROWNUM BULK COLLECT INTO v_tab FROM dual CONNECT BY LEVEL < 100000; FORALL i IN 1..v_tab.COUNT INSERT INTO test VALUES (v_tab(i)); END; / -- 2.6 sec 

Insert

 -- test table CREATE global TEMPORARY TABLE test (id number) ON COMMIT preserve ROWS; BEGIN INSERT INTO test SELECT ROWNUM FROM dual CONNECT BY LEVEL < 100000; END; / -- 1.4 sec 

direct path insert http://download.oracle.com/docs/cd/B10500_01/server.920/a96524/c21dlins.htm

 BEGIN INSERT /*+ append */ INTO test SELECT ROWNUM FROM dual CONNECT BY LEVEL < 100000; END; / -- 1.2 sec 

Answer 2

It depends on the nature of the work you are doing to fill in the intermediate results. If the work can be done relatively simply in the SELECT statement for INSERT, which will usually work better.

However, if you have complex intermediate logic, it may be easier (in terms of code maintenance) to retrieve and insert data in batches using bulk collection / bindings. In some cases, it can be even faster.

It is very important to note: the query plan used by INSERT INTO x SELECT ... will sometimes be different from that used when the query is executed on its own (for example, in an explicit PL / SQL course). When comparing performance, you should consider this.

Answer 3

Paste in select must be faster. First, skip overhead to store data in the collection.

Answer 4

Tom Kite from one hundred fame answered this question more firmly. If you want to do some searching, you can find a question and answer that contains detailed test results and explanations. It shows the cursor plsql vs. plsql bulk collect, including the effect of periodic commit, as well as the sql tab as a choice.

Paste, because the choice wins by hand all the time, and the difference even on modest data sets is dramatic.

That said. previously a comment was made on the complexity of intermediate calculations. I can think of three situations where this would be relevant.

1) If the calculations require going beyond the limits of the Oracle database, it is clear that a simple insert as an option does not do the trick.

2) If the solution requires the use of PLSQL function calls, then context switching can potentially kill your query, and you may have better results when calling plsql functions called by plsql. PLSQl was made to invoke SQL, but not vice versa. Thus, calling PLSQL from SQL is expensive.

3) If the calculations make the sql code very difficult to read, although although it may be slower, the solution for collecting the sum of plsql may be better for these other reasons.

Good luck.

Answer 5

When we declare the cursor explicitly, oracle will highlight the private SQL workspace in our RAM. When you have a select statement that returns multiple rows, it will be copied from the table or view to the private SQL workspace as ACTIVE SET. Its size is the number of rows matching your search criteria. When the cursor opens, the cursor will be placed on the first line of the ACTIVE SET. Here you can execute DML. For example, if you perform some update operation. It will update any changes to rows in the workspace, not directly in the table. Therefore, it does not use the table every time we need to update. It is retrieved once to the workspace, and after the operation is completed, the update will be performed once for all operations. This reduces the transfer of I / O data between the database and the user.

Answer 6

I suggest using an explicit PL \ SQL cursor, and only to perform any DML operation in the private workspace allocated to the cursor. This will not affect database server performance during peak hours.