CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloperplsqldeveloper
ProCeng
ProCeng

Reputation: 33

"truncate-create as select" instead of update

I have a table with big data, and I want to update it. I have a method to do that. Frist, copying the original table and trunc it. After that I will use create as select from copy of table. So I wont use update expressions. Is it more effective?

Upvotes: 0

Views: 786

Answers (2)

eliatou
eliatou

Reputation: 744

Whhi truncate the table? I assume there is no constraint on your table (if you can't do a truncate!). You are more simple whit the commande rename; It will be quicly as the others methods. You can do something like that (I take your code):

Your code:

truncate  table table1; 
insert into table1 select * from table1_copy

replace by:

drop table table1 ;
alter table table1_copy rename to table1;
recreate index of  table1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The answer to your question really depends on how many rows are affected by the update. Obviously, copying 100,000,000 rows into another table to update one row is not effective. To update all of them it might be beneficial, because inserts are usually faster than updates.

Before going down this path, I would want to be understand how many rows are being updated. If not very many are, then appropriate indexing might improve the performance of the updates.

However, the logic for your approach isn't quite right. I would be inclined to do:

create table temp_copy as
    select . . .
    from bigtable . . .;

Your logic goes in the select. Then, truncate the big table and reinsert:

truncate table bigtable;

insert into bigtable
    select *
    from temp_copy;

Upvotes: 1

Related Questions