update large table
I have a big table like 40 million rows....I want to update it as fast as I could
without using redologs (logging) or rollback or temp etc. Is this possible?
Does this command help?
update table EMP nologging parallel 8 set fullname=upper(trim(fullname));
thanks a lot
no you cannot do that hope you reliase how dangerous it is too even if you could
pls tell me dear why....
I suggest you to read oracle concepts manual
tri writing a procedure to do that.it realy decrease the time used to do an update on such a large table.
It realy works for me on a table with over 32 mil rows.
However , it will take a lot of time(and resource) if you use the 'clasy' way !
why is a procedure quicker than a simple sql statement
Without logging? Sure -- disable all constraints against the table, do a CREATE TABLE AS SELECT that creates the modified table, drop the existing table, rename the new table to the old table, then re-enable constraints.
redo - yes
temp - I dont think its going to use temp because no sorting will happen
rbs - I'm afraid no.
Trim wouldn't really help unless your datatype is char or your data is
from other source.
but this certainly will help you, I call it the REYDP_HUGEUPDATE.
cursor blockcols is
select distinct substr(ROWID,10,6)
fetch blockcols into t_block_no;
exit when blockcols%NOTFOUND;
UPDATE EMP SET fullname=UPPER(TRIM(fullname))
WHERE SUBSTR(ROWID,10,6) = t_block_no;
PL/SQL Block always runs slower than a single update.
For a simple update, do it in a single statement. If possible, use parallel DML.
Originally Posted by tamilselvan
can you be more specific about parallel DML? what exaclty do you have in mind?
Click Here to Expand Forum to Full Width