What is the fastest way to insert data into an Oracle table?
Table has 25 million records. I have to add new column datatype is date within this table and copy data into this new column from old column under same table but old column has timestamp datatype. I'm doing following steps, Are you able to please tell me every other way i'm able to get it done. After i run follwing queries it running six or seven hrs after which i must kill it. Database is oracle.
alter table ofr_ft rename to ofr_ft_bkup; CREATE TABLE ofr_ft ( all old columns, age DATE NOT NULL, CONSTRAINT ofr_ft_pk PRIMARY KEY (ofr_ft_id) ); INSERT INTO ofr_ft (old coumns, age) (values from old columns, cast(date_last_chng as date) FROM ofr_ft_bkup); COMMIT;
Why would you like to produce a new table?
alter table mytable add (newcolumn date); update mytable set newcolumn = oldcolumn; alter table mytable drop (oldcolumn);
when the update does not work since the rollback segment is simply too small, something of that nature should have the desired effect:
alter table mytable add (newcolumn date); begin loop update mytable set newcolumn = oldcolumn where oldcolumn is not null and newcolumn is null and rownum<=10000; exit when sql%rowcount=0; commit; end loop; end; / alter table mytable drop (oldcolumn);
It's often faster to disable the secrets first, perform the card inserts, then let the secrets after.
Also, investigate whether it's faster if not inside a transaction.