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....:confused:

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.

Quote:

---

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

Quote:

---

Originally Posted by dgcampos

tamilselvan :

can you be more specific about parallel DML? what exaclty do you have in mind?

regards,

daniel

---

Please read my paper.

http://oracleact.com/papers/oracle_parallel_dml.html

Dear Tamil, While trying example provided in the link given by you I got the following problem

Code:

---

SQL> SELECT count(*) FROM myobjects;

  COUNT(*)
----------
  10427392

Elapsed: 00:00:13.06

---

I'm trying to run Parallel DML on that table, I have following index on that table

Code:

---

SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = 'MYOBJECTS';

INDEX_NAME                                        COLUMN_NAME
------------------------------ ------------------------------
INDX_MYOB                                          OBJECT_NAME

Elapsed: 00:00:00.01

---

Here is the code for Parallel DML, I'm running only one session which is current. I dont know how comes I got an error saying "DEADLOCK DETECTED"

Code:

---

SQL> alter session enable parallel dml;

Session altered.

SQL>
SQL> alter session set db_file_multiblock_read_count=128;

Session altered.

SQL> set timin on
SQL>
SQL>
SQL> UPDATE /*+ full(myobjects) parallel(myobjects, 8)*/ myobjects
  2  SET object_name = upper(object_name);
update /*+ full(myobjects) parallel(myobjects, 8)*/
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P015
ORA-00060: deadlock detected while waiting for resource

---

Quote:

---

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.

---

Yes Tamil, I do agree with you.
In fact I myself have posted before,
long time ago about using PARALLEL DML
also here in this forum.


PL/SQL Block always runs slower than a single update.-
- yes, it was actually a sort of a test to the poster. Davey, actually posted contesting the use of pl/sql to update before my post.

It'a trick that will work for small table
but not in huge one...heheh..my bad.

So, to summarize the thread,

Quote:

---

Originally Posted by davey23uk

no you cannot do that hope you reliase how dangerous it is too even if you could

---

To TABREAZ,

You need to increase the INITRANS value for the index.

See below:

create index t1_idx on t1(object_name)
pctfree 30 initrans 9
tablespace large_01_idx nologging parallel 8
/
11:10:32 SQL> alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.00
11:10:40 SQL> alter session force parallel dml parallel 8 ;

Session altered.

Elapsed: 00:00:00.00
11:11:25 SQL> update /*+ full(t1) parallel(t1,8) */ t1
11:11:34 2 set object_name = lower(object_name);

1855968 rows updated.

Elapsed: 00:04:54.27

Thanks Tamil,

Its working fine now. Thanks a lot

Quote:

---

Originally Posted by Bore

So, to summarize the thread,

---

So whats the danger plsssssssssssssss :confused:

Why is TAMIL doing it

tamil isnt doing it

There are some occasions you need to update a big table.
For example, a ETL process may need to clean up the source data in a staging table in a DW system before loading into FACT table.

It's nothing wrong using parallel DML as long as you achieve the goal in a shorter time.

If you are not using the feature, then what's the purpose of it?

1) When we talk about danger we mean update with no redo generation
2) There is no danger since that's not possible
3) If you do not generate redo and the database fail and you bring the file from backup, you'll loose your table

yeah....precisely dear:D

Its just an ETL table and I can afford to redo it in case of error
coz i got a backup copy.
I am converting a Foxpro database to Oracle. Its a Loans Payment table.
I got 180million plus Loans Payment foxpro table,
the tables reside on different branches(decentralized),
and its branches have different structure of that table (:rolleyes: what a
delimma) because each branch has its own programmer,
with different style to maintain the apps:D.
I have to merged it in the central office.
I built a common structure table that fits for all the branches.
Then I extract using HS connectivity.
I start cleaning it up ...updates...updates...updates...
yeah the parallel mention by tamil may help.
I also partitioned it with the loan_date per year.

One of my delimma is there are lots of erroneous loan_date. So
I cannot load it into the partitioned table now. But the management
tell me that all the records must be accounted for ...that all of it must
be inquired (seen in a inquiry form) even if errorneous as it is.
How can I merged this erroneous date rows to the main partitioned table?

I am loading the erroneous loan_date rows to another table then make
a VIEW to join the two. Is this the right way hunnies?:)


Thanks

In 10g there is one underscore parameter, _disable_logging whose default value is FALSE, that enables logging the transaction details in the redo.

It is not advisable to change the value in any environment.

In your test environment, you can set it to TRUE, and run the load process. See that helps.

As I said, do not change the parameter in production database.

if not to do it in a production database, then why should you ever so it in a TEST database, you know the place where you test what goes into production. Thats just crap advice

Jennifer2007,

I think you are building the database for the first time and the big update is going to be one time job. And you wanted to update the table in a shorter time.

1 Take DB backup.
2 Change the underscore parameter, _disable_logging to TRUE.
3 Drop indexes on the table and Update the table using parallel options.
4 Verify the updated columns in the tables.
5 Shutdown the DB and change the parameter, _disable_logging to FALSE.
6 Startup the DB. Verify integrity of the DB (all the tables and indexes).
7 Take a backup now.

In case some thing goes wrong during the update, restore the backup copy taken in step-1.

Quote:

---

Originally Posted by davey23uk

if not to do it in a production database, then why should you ever so it in a TEST database, you know the place where you test what goes into production. Thats just crap advice

---

I can call it a STAGING environment, instead of TEST.

thanks everyone;)