CODEWITHSUNDEEP
oracle-database
Elingela
Elingela

Reputation: 819

Oracle trying to update a column

I have a table containing some billion records and just need to update a single field with no condition.

update <table name> set flag='N' this is my update query, it takes a very long long time. How to make this execute fast.

Upvotes: 2

Views: 249

Answers (3)

tbone
tbone

Reputation: 15473

If you really cannot wait that long to run your update, and can live with the flag being null instead of 'N', then you can do this very quickly (untested, but should work):

alter table my_tab set unused column flag;

alter table my_tab add (flag char(1));

You can later drop unused with checkpoints if you care to reclaim some space (note that the drop will take some time, so be careful if you choose to do this).

Hope that helps

EDIT: Thanks to @TTT for this article, apparently in 11g Oracle can store a default value in the data dictionary rather than performing an update to all rows (what I expected from previous experiences). This means you can use 'N' value instead of NULL. Just make sure you specify NOT NULL as well as the default value:

SQL> set timing on
SQL> drop table test1
Table dropped.
Elapsed: 00:00:00.23

SQL> create table test1
(
col1 varchar2(10),
flag char(1)
)
Table created.
Elapsed: 00:00:00.14

SQL> insert into test1
select 'x','Y'
from dual
connect by level <= 1000000
1000000 rows created.
Elapsed: 00:00:02.09

SQL> alter table test1 set unused column flag
Table altered.
Elapsed: 00:00:00.07

SQL> alter table test1 add (flag char(1) default 'N' not null )
Table altered.
Elapsed: 00:00:01.01

Without the "NOT NULL" and just a default, it took over 20 secs, showing this "fast=true" trick only works when specifying not null and a default (which makes sense really).

Upvotes: 2

Florin Ghita
Florin Ghita

Reputation: 17643

First ideea: If you have space: 

  create table tmp_tab as select col1, ..., coln, "N" as flag 
  from your_tab; 

  rename table your_tab to your_tab_old; 

  rename tmp_tab to your_tab;

will very fast compared to simple update.

Drop your_tab_old if everything is ok.

Second ideea:

update /*+parallel(your_tab 8)*/ your_tab set flag='N';

will be faster than noparallel version.

Upvotes: 0

Erkan Haspulat
Erkan Haspulat

Reputation: 12562

Do not update. Use CTAS, and then rename the new table.

CREATE TABLE T_NEW NOLOGGING PARALLEL AS
SELECT COL1, COL2, 'N' FLAG FROM T_OLD;

Then apply indexes, constraints or grants from your old table. If you forget this step you will suffer.

DROP TABLE_T_OLD;
RENAME T_NEW TO T_OLD;

Try to be careful since you're dropping the old table.

Upvotes: 0

Related Questions