How to handle big data update based on excel spreadsheet

Question

Scenario

1. Select-based data is exported from DB to Excel spreadsheets.
2. Some manual work is done on the excel files (filling missing data).
3. Changed data from excel files is updated back to DB.

Environment

• Oracle DB
• Original table is about 7 million rows
• Can't use import tools in SQL Developer etc, must be a plain SQL script.
• There are several excel files with couple hundred of thousand rows each (400-700 thousand)

Current solution

There is an UPDATE statement generated for each row of the excel file. It checks if data has been changed (the file has separate columns for old and new values). So from each file we get hundreds of thousands of UPDATE statements.

Question is...

This solution is fairly simple, but I wonder if there is a better way to do that?

Answer 1

A Fast Method

A probably faster way to do this is the following:

1. Provide the data in a database table
2. Execute a MERGE statement that merges the data from this table into the source table

To provide the data in a database table you can use sql loader or [external tables].

http://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606

create table big_tab(
  id number primary key,
  value1 number,
  value2 number,
  value3 number)
;

create table mod_tab(
  id number,
  value1 number,
  value2 number,
  value3 number)
;

Now assume you have the following data in big_tab.

SQL> select * from big_tab;

    ID     VALUE1     VALUE2     VALUE3

---

     1         11         21         31
     2         12         22         32
     3         13         23         33
     4         14         24         34
     5         15         25         35

you have a csv file mod_data.csv with the following values

1,111,121,131
3,113,123,133

you can load this into the table with sqlldr load.ctl DIRECT=true with the sql loader control file sql.ldr containing

LOAD DATA
INFILE "mod_data.csv"
TRUNCATE
INTO TABLE mod_tab
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  id char,
  value1 char,
  value2 char,
  value3 char
)

Using the option "DIRECT=true" will be faster thean loading without this option. Now you have

SQL> select * from mod_tab;

        ID     VALUE1     VALUE2     VALUE3
---------- ---------- ---------- ----------
         1        111        121        131
         3        113        123        133

you can merge this into big_tab with the following statement

MERGE INTO big_tab 
using mod_tab on (big_tab.id=mod_tab.id)
when matched then update
  set 
    value1=mod_tab.value1,
    value2=mod_tab.value2,
    value3=mod_tab.value3
;

and get

SQL> select * from big_tab;

        ID     VALUE1     VALUE2     VALUE3
---------- ---------- ---------- ----------
         1        111        121        131
         2         12         22         32
         3        113        123        133
         4         14         24         34
         5         15         25         35

If the processing was ok you can cleanup

    truncate table mod_tab;

An Even Faster Method

If you have a lot of data to modify, there is a way that is even better (at least if your io system is fast):

1. create a auxiliary table with the same structure as big_tab table
2. insert the modify rows in this auxiliary table
3. insert the rows from big_table that will not be modified in the auxiliary table
4. drop the big_tab table
5. rename the auxiliary table to big_tab.
6. add constraints and indexes to the new big_tab table

This can be don by the following script

create table aux_tab as select * from big_tab where 1=0;
alter table mod_tab add  primary key (id);
insert /*+ APPEND */ into aux_tab select  big_tab.id id,
    decode(mod_tab.id,null,big_tab.value1,mod_tab.value1) value1,
    decode(mod_tab.id,null,big_tab.value2,mod_tab.value2) value1,
    decode(mod_tab.id,null,big_tab.value3,mod_tab.value3) value3
  from big_tab, mod_tab
  where big_tab.id=mod_tab.id(+)
;
drop table big_tab;
rename aux_tab to big_tab;
alter table big_tab add  primary key (id);
alter table mod_tab drop  primary key;
truncate table mod_tab;

And Even Faster

You should always do your load and insert in direct path mod. Therefore I added and APPEND hint to the insert statement and the DIRECT=true option to the SQL*Loader command. But you can try to parallelize some actions to get even faster. Parallel statement work optimal on partitioned tables so it may make sense to partition big_tab and also mod_tab and aux_tab.

Answer 2

Instead of thousands of updates, you could insert the data into temporary source table and then run merge against the target table.

merge into target_table t
using source_table s
on (t.id = s.id)
when matched then update set t.column1 = s.column1, t.column2 = s.column2, ...;

Answer 3

Definately, exporting that many rows to a tool like Excel, sounds like the worst solution to what sounds like a normal action. This being : updating data.

In the opening post, I don't see any reason why the data has to leave the Oracle database. Certainly not to a tool like Excel.