Parallel statement work optimal on partitioned tables so it may make sense to partition big_tab and also mod_tab and aux_tab. But you can try to parallelize some actions to get even faster. Therefore I added and APPEND hint to the insert statement and the DIRECT=true option to the SQL*Loader command. You should always do your load and insert in direct path mod. Insert /*+ APPEND */ into aux_tab select big_tab.id id,ĭecode(mod_tab.id,null,big_tab.value1,mod_tab.value1) value1,ĭecode(mod_tab.id,null,big_tab.value2,mod_tab.value2) value1,ĭecode(mod_tab.id,null,big_tab.value3,mod_tab.value3) value3Īlter table big_tab add primary key (id) This can be don by the following script create table aux_tab as select * from big_tab where 1=0 Īlter table mod_tab add primary key (id) add constraints and indexes to the new big_tab table.insert the rows from big_table that will not be modified in the auxiliary table.insert the modify rows in this auxiliary table.create a auxiliary table with the same structure as big_tab table.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): If the processing was ok you can cleanup truncate table mod_tab You can merge this into big_tab with the following statement MERGE INTO big_tab Using the option "DIRECT=true" will be faster thean loading without this option. You can load this into the table with sqlldr load.ctl DIRECT=true with the sql loader control file sql.ldr containing LOAD DATAįIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' You have a csv file mod_data.csv with the following values SQL> select * from big_tab ID VALUE1 VALUE2 VALUE3 Now assume you have the following data in big_tab. To provide the data in a database table you can use sql loader or.
0 Comments
Leave a Reply. |