-
update large table
Hi friends,
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
-
hi,
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.
DECLARE
t_block_no VARCHAR2(18);
cursor blockcols is
select distinct substr(ROWID,10,6)
from emp;
begin
open blockcols;
LOOP
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;
commit;
END LOOP;
close blockcols;
END;
/
---good luck--------
---------------
-
Ray,
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
Ray,
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.
tamilselvan :
can you be more specific about parallel DML? what exaclty do you have in mind?
regards,
daniel
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|