Oracle Performance – How to Update 700 Million Rows Efficiently

oracleperformance

I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows.

I don't have admin access, or access to an admin, so this needs to be accomplished with basic sql and no temp table creates.

Further complicating matters is if I try to just do a simple update where 1=1, it runs out of redo space.

The way I have it running right now is looping such as this:

loop
  update mytable set mycolumn = '1' where mycolumn is null and rownum < 50000;
  commit;
end loop

but I know this is probably naive and there must be a quicker and more elegant solution.

Best Answer

If you have the space, you can CTAS using minimal undo/redo. If you have any indexes at all, doing it any other way will be very slow and generate logging like crazy.

In the case where you have a single IOT without any secondary indexes, or a single table cluster, you could step through the primary/cluster key updating in chunks without having to rescan the whole table to find the fields that have not yet been updated.

--edit

I am not able to create a secondary table ... There are a couple of indexes, but none of them involve the column I am updating.

Then I suggest break the table up into chunks for processing using something you are indexing on (even if it is a single column, you can split it into ranges of values) This will do a FTS once instead of once for each chunk as in your code. You will have to live with an awful lot of redo and will wipe out your undo space too (so no flashback subsequently)

--edit2

if you can add/rename/drop columns, you can do this very efficiently, but only on 11g

Related Solutions

Updating rows conditionally with a gapless ascending value

See this discussion on AskTom about the way expressions are evaluated. The decode function does perform short-circuit in most cases: right-side expressions are not evaluated if a condition on the left is evaluated to true. Sequence are special however, they are evaluated for all lines in all cases.

The easiest workaround is to use a function that calls the sequence:

SQL> CREATE SEQUENCE seq;

Sequence created

SQL> CREATE OR REPLACE FUNCTION f RETURN NUMBER
  2  AS
  3     l_result NUMBER;
  4  BEGIN
  5     SELECT seq.nextval INTO l_result FROM dual;
  6     RETURN l_result;
  7  END;
  8  /

Function created

SQL> UPDATE t1 SET letter='x',
  2     number2 = DECODE(letter,'a',f,'d', f,'e', f,NULL);

7 rows updated

SQL> select * from t1;

LETTER    NUMBER1    NUMBER2
------ ---------- ----------
x               1          1
x               2 
x               3 
x               4          2
x               5          3
x               6 
x               7 

7 rows selected

As you can see the function is evaluated only when the DECODE expression requires it.

You can work a pure SQL solution in most cases. If we assume that the table has a PK (I'll take number1), this self-join SQL solution would work:

SQL> UPDATE t1 t1_out
  2     SET letter  = 'x',
  3         number2 = CASE WHEN letter IN ('a', 'd', 'e') --
  4                        THEN (SELECT COUNT(*)
  5                                FROM t1 t1_in
  6                               WHERE letter IN ('a', 'd', 'e')
  7                                 AND t1_in.number1 <= t1_out.number1)
  8                   END;

7 rows updated

SQL> select * from t1;

LETTER    NUMBER1    NUMBER2
------ ---------- ----------
x               1          1
x               2 
x               3 
x               4          2
x               5          3
x               6 
x               7 

7 rows selected

You have probably tried an analytics solution. It is possible to write a query that will return the appropriate result set but update and analytics don't work very well together (analytics tend to produce non-updatable views).

Exporting 30 million rows to CSV

Asuming this is to transport data to an other system. It that case this will work:

set colsep ";"
set linesize 9999
set trimspool on
set heading off
set pagesize 0
set wrap off
set feedback off
set newpage 0
set arraysize 5000
spool you csv_file.csv
select rows from your tables;
spool off

If you don't want a header line, change to heading off

If this is to analyze the data in excel, please wake up and learn sql. SQL, especially Oracle, has many great tools to help analyzing your data. By the time the data is transported into the csv file, Oracle analytics has already done some great analysis.Data Warehousing and Business Intelligence but for many tasks plain SQL can be good enough. Start reading about Oracle Analytics.

Best Answer

Related Solutions

Updating rows conditionally with a gapless ascending value

Exporting 30 million rows to CSV

Related Question