Update huge tabel

[farrokhp]

How can I update a big table without blowing rollback segments ?

I cannot open a cursor and commit every n records, because the cursor will close.

What is the best way ?

[pando]

what do you mean the cursor will close...?

[marist89]

I'm assuming you mean you will get an ORA-1555 error message.

What's your table structure look like?

Depending on your structure, you could open up multiple cursors, each with say 10% of the rows.

[farrokhp]

This is my table structure :

CREATE TABLE POSTAL_CODE (
POSTAL_CODE_STRING VARCHAR2 (7) NOT NULL,
SEQUENCE_NUMBER NUMBER (38) NOT NULL,
PROVINCE_CODE VARCHAR2 (2) NOT NULL,
CITY_NAME VARCHAR2 (30) NOT NULL,
AREA_CODE VARCHAR2 (3) NOT NULL,
TIME_ZONE_NUMBER NUMBER (38) NOT NULL,
DAYLIGHT_SAVING_FLAG_STRING VARCHAR2 (1) NOT NULL,
LAST_UPDATE_DATETIME DATE NOT NULL,
ROW_UPDATE_NUMBER NUMBER (38) NOT NULL,
CONSTRAINT POSTAL_CODE_PK
PRIMARY KEY ( POSTAL_CODE_STRING, SEQUENCE_NUMBER )


Can you be more specific ? Thanks guys

[marist89]

[assumption]sequence_number is your PK[/assume]

I would update using area_code as a "batch".

get all the area codes
for each area code
   open your cursor
   do your updates
get next area code

[pando]

well I meant a normal cursor would do no? I dont understand what do you mean you cannot commit every N rows, if you commit every row then it is bad but every 5000 seems reasonable no?