Will half a million records with the same date value as the non-unique index in the column for that date?

Question

I have a cursor that selects all the rows in a table, just over 500,000 rows. Read the line from the INSERT cursor to another table that has two indexes, neither one, not one, not one type of DATE. COMMIT. Read the next line from the cursor, INSERT ... until the cursor is empty.

All DATE column values ​​match, starting at the timestamp initialized at the beginning of the script.

This thing worked for 24 hours, only posted 464K lines, a little less than 10K lines / hour.

Oracle 11g, 10 processors (!?) Something must be wrong. I think the DATE index is trying to process all of these records with exactly the same value for this column.

Answer 1

Why don't you just do:

insert into target (columns....) 
select columns and computed values 
from source

commit

?

This slow, slow action does far more performance damage than an index, which may not make any sense.

Answer 2

Indexes slow down inserts, but speed up queries. This is normal.

If this is a problem, you can remove the index, insert rows, and then add the index again. It can be faster if you do many inserts at once.

The way you copy data using cursors seems inefficient. Instead, you can try to install the set:

INSERT INTO table1 (x, y, z)
SELECT x, y, z FROM table2 WHERE ...

Answer 3

. , , .

- , , ( ), , , . , , , .

, , insert into ... select from.

Answer 4

, , , , db . , , ? .

Answer 5

, FORALL BULK COLLECT. DDL .

create or replace procedure fast_proc is
    type MyTable is table of source_table%ROWTYPE;
    MyTable table;
    begin
        select * BULK COLLECT INTO table from source_table;

         forall x in table.First..table.Last
             insert into dest_table values table(x) ;
    end;

Answer 6

, , , - . 500 000 .

INSERT... SELECT FROM.... , . SQL, PL/SQL.

, /* + APPEND */hint - , .

o 10 , SQL, 10 pl/sql, 10 .

Oracle 10 ( parallelism), Oracle 11.2 DBMS_PARALLEL_EXECUTE.

/ BULK COLLECT - 1000 ( ). , DBMS_PARALLEL_EXECUTE, DBMS_JOB.

(: - Oracle 10)