0

It's required to update columns with metadata globally in all rows of all tables in DB. Let's say every table has a column MY_META and the aim is roughly

update ANOTHER_TABLE set MY_META = 'HELLO'

for each table.

Estimated total rows count is 2e9.

Suppose ARCHIVELOG mode is on, so during those updates a lot of extra space is going to be consumed.

The process of updates is planned to be running in production DB simultaneously with business transactions, which should not be lost.

The simplest thing to start with is temporarily installing plenty of hardware for ARCHIVELOG files.

Is there an elegant way to achieve the same goal programmatically or via tuning "secret options"?

1 Answer 1

1

Of course. Do not perform an UPDATE at all. Just add it as new column with the default value of HELLO.

It took 0.12 seconds and generated an insignificant amount of redo in my sandbox:

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

SQL> set timing on
SQL> select count(*) from t1;

  COUNT(*)
----------
1000000000

Elapsed: 00:00:14.34

SQL> select sn.name, ss.value from v$statname sn 
     join v$sesstat ss on (sn.statistic# = ss.statistic#)
     where ss.sid = sys_context('userenv', 'sid') and sn.name = 'redo size';

NAME            VALUE
---------- ----------
redo size        5964

Elapsed: 00:00:00.00

SQL> alter table t1 add my_meta varchar2(20 char) default 'HELLO' not null;

Table altered.

Elapsed: 00:00:00.12
SQL> select sn.name, ss.value from v$statname sn 
     join v$sesstat ss on (sn.statistic# = ss.statistic#)
     where ss.sid = sys_context('userenv', 'sid') and sn.name = 'redo size';

NAME            VALUE
---------- ----------
redo size       20408

SQL> select count(*) from t1 where my_meta = 'HELLO';

  COUNT(*)
----------
1000000000

Elapsed: 00:01:00.96
5
  • That's impressive. The only problem is that NOT NULL is a significant part. Without it altering table takes lot of time and redo space. The same we have when afterwards trying to modify the column to be nullable.
    – diziaq
    Commented Aug 8, 2018 at 8:24
  • The trick Balazs had shown is a feature of 11g. With this syntax, the database is only modifying the metadata for the table. Commented Aug 8, 2018 at 10:10
  • @MichaelKutz, yes, actually this solution is valid while we use it separately in one DB. For instance, dump of this table would still have NULLs in MY_META column and it may cause problems.
    – diziaq
    Commented Aug 9, 2018 at 8:58
  • @diziaq And the import of the dump will also set the metadata and the result will be the same. Commented Aug 9, 2018 at 9:18
  • On Oracle 11 (any release) a dump of the table will contain NULL instead of values for my_meta - it's tested. I suppose in Oracle 12 there's a similar situation, and it's obvious that we cannot get that blazing fast default values for millions of rows for free. Anyway, I find your answer useful, so I've upvoted.
    – diziaq
    Commented Aug 13, 2018 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.