Quick way to reset all column values to a default

I'm converting data from one schema to another. Each table in the source schema has a 'status' column (default NULL). When a record has been converted, I update the status column to 1. Afterwards, I can report on the # of records that are (not) converted.

While the conversion routines are still under development, I'd like to be able to quickly reset all values for status to NULL again.

开发者_运维百科

An UPDATE statement on the tables is too slow (there are too many records). Does anyone know a fast alternative way to accomplish this?

---

The fastest way to reset a column would be to SET UNUSED the column, then add a column with the same name and datatype.

This will be the fastest way since both operations will not touch the actual table (only dictionary update).

As in Nivas' answer the actual ordering of the columns will be changed (the reset column will be the last column). If your code rely on the ordering of the columns (it should not!) you can create a view that will have the column in the right order (rename table, create view with the same name as old table, revoke grants from base table, add grants to view).

The SET UNUSED method will not reclaim the space used by the column (whereas dropping the column will free space in each block).

---

If the column is nullable (since default is NULL, I think this is the case), drop and add the column again?

---

While the conversion routines are still under development, I'd like to be able to quickly reset all values for status to NULL again.

If you are in development why do you need 70 million records? Why not develop against a subset of the data?

---

Have you tried using flashback table?

For example:

select current_scn from v$database;
-- 5607722 

-- do a bunch of work

flashback table TABLE_NAME to scn 5607722;

What this does is ensure that the table you are working on is IDENTICAL each time you run your tests. Of course, you need to ensure you have sufficient UNDO to hold your changes.

---

hm. maybe add an index to the status column.

or alterately, add a new table with the primary key only in it. then insert to that table when the record is converted, and TRUNC that table to reset...

---

I like some of the other answers, but I just read in a tuning book that for several reasons it's often quicker to recreate the table than to do massive updates on the table. In this case, it seems ideal, since you would be writing the CREATE TABLE X AS SELECT with hopefully very few columns.