unique constraint violated error performance

Question

I am inserting in a table millions of records , such operation will need hours or maybe a day. After 2 hours the connection through my pc was disconnected, so I want to repeat the insert from the start.
My Question
which is faster ? truncate the table and repeat it again , or creating a primary key and continue, however an error will be raised because of 'unique constraint violated' for every record that was inserted in the last 2 hours.

How are you doing the insert? Why repeat from the start if some data was committed already - wouldn't starting from the first record that wasn't inserted the first time make more sense? (Hopefully you aren't inserting/committing one row at a time... and don't have any indexes or triggers yet). — Alex Poole
@AlexPoole I have a table that contain million of records , I want to divide it into 4 tables. the original table has date column ( year 1 year 2 year 3 and year 4 ) so for every year in the original table I am inserting the data into table 1 . So in my scenario I was inserting the data from orignal to the 4 tables . — Moudiz
But by what mechanism? Are you doing insert into new_table_1 select ... from orig_table where year = ..., or selecting all the records, looping over them, and deciding which table to insert each record too one-by-one? And in what environment/language - sounds like maybe you're pulling the entire table back to your PC over the network and then pushing it back to the four new tables, which will be slow, rather than doing all the work within the DB. That isn't really what you're asking about, of course. (And have you considered partitioning?). — Alex Poole
@AlexPoole using paritionining will help me in performance wise , how ever dividing that through 4 tables will help me because of a busninse in the application. I am doinging that through a cursore ..fetching through the primary count and I include a condition so they insert into tables I am using plsql — Moudiz
Single inserts for each record returned from the cursor, or are you at least doing batch inserts? A single insert (selecting with your condition) would still be faster, as long as you have enough undo. I've gone rather off-piste though, the question isn't really about why it's so slow. If you're doing it in PL/SQL anyway you could submit a job to run that, so it's not reliant on your client being connected. Truncating will likely be faster than creating a primary key (on what?) and relying on exceptions, if you can't pick up where you left off. But it depends a bit on how far through you are. — Alex Poole

Maheswaran Ravisankar Maheswaran Ravisankar · Accepted Answer · 2014-01-09T16:57:00

Truncating the table (If full refresh)is the best option Hands down. There's also SKIP parameter, if you use Oracle's SQL*Loader utility. Let me explain to some extent!

Also try loading the table with SQL*Loader using DIRECT load option. Which means loading the table by loading into the data blocks, instead of conventional INSERT statements.

By this kind of loading, you can enable UNRECOVERABLE , which means no/less redo log written, so the loading is very fast >70% than conventional INSERT.

But, the downside of this loading is, ALL indexes on this table, except NULL constraints will be made UNUSABLE, before the start of loading, and the data will be loaded. And on SUCCESSFUL completion, SQL*Loader tries to re-enable the index, by rebuilding it. So, if in case my any reason, the loading had interrupted, the error messages will be logged properly, and the index would be left UNUSABLE.

More Details on : Please find Here
(DIRECT/CONVENTIONAL Loading)

Also, using SQL*Loader, you can load using Conventional loading, which means SQL*Loader would generate the chunk of INSERTs using the file , and process it. In this type of loading, all the INDEXES will be left as such, and the table remains unharmed.

If at all any error happens, SQL*Loader will log a SKIP parameter, which means , by next run, if you specify that number, the table will loaded from that point of the file.

More Details on SQL*Loader : Here

unique constraint violated error performance

2 Answers