CODEWITHSUNDEEP
sqloracle-database
beckham
beckham

Reputation: 125

Performance tuning in Oracle - update same column

I am writing a query in Oracle to update the column based on same column

UPDATE TABLE SET A = 'CG-'||A

I HAVE THE DATA LIKE 

COLUMN A
121
234
333

I NEED THE DATA LIKE

COLUMN A
CG-121
CG-234
CG-333

basically I am doing this for 30 Million records and its taking lot of time. Is there any way I can optimize this query?. If I create a Index on Column A does that improve the performance?

Upvotes: 0

Views: 93

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You have the correct query:

UPDATE TABLE
    SET A = 'CG-' || A;

Here are different options.

First, you can do this in batches, say 100,000 rows at a time. This limits the size of the log.

Second, you can do a "replace" rather than update:

create table tempt as 
    select * from table;

truncate table "table";

insert into table ( . . . )
    select 'CG-' || A, . . . 
    from tempt;

Third, you can use a generated column and dispense with the update (but only in the most recent versions of Oracle).

Upvotes: 1

Related Questions