How to improve the efficiency of Oracle big data table Update

Source: Internet
Author: User

In Oracle, if the table data volume is large (M-level or larger), updating a field is very slow (for example, updating the historical business process table in my HIS project, 1.6 million records, use CURSOR to update, 1000 COMMIT once, it took four days to complete the update), and later tried to improve:

1. Cancel LOGGING on the table

2. Cancel the INDEX of the table

But it is still very slow, but you can find this:

Http://asktom.oracle.com/pls/asktom/f? P = 100: 11: 0: P11_QUESTION_ID: 6407993912330

In this Q & A question, ORA officially proposed a solution:

1. CREATE table as select xxxxx to generate a new table T1

2. Create the same index as the target table on T1

3. Delete the target table or RENAME (pay attention to backup for regret)

4. Rename T1 as the target table

I tried it and it was very fast. My task was almost completed in 2 minutes.

For example, csywdk. table_room is a large table. You need to delete the record of bakfwid in noNewYWFW20081205 and update ROOM_LOC recorded by bakfwid in imp_table_room to imp_table_room.room_loc:

(1) create a new table

Create table tmp_new_table_room081205

Select t1.ROOM _ ID, t1.NEWROOMID, t1.BUILDID, t1.TFH, t1.DKH, t1.BUILD _ NO, t1.LAYER _ NO, t1.ROOM _ NO, t1.ROOM _ NAME,

Decode (t2.bakfwid, null, t1.ROOM _ LOC, t2.room _ loc)

T1.ROOM _ AREA,

T1.SURTYPE, t1.LAYER _ NAME, t1.DEVDEP, t1.CELL, t1.DELFLAG, t1.QXXZ, t1.SJSJLSH, t1.FD, t1.ID, t1.BAKFWID

From csywdk. table_room t1 left join imp_table_room t2 on t1.bakfwid = t2.bakfwid

Where not exists (select 1 from noNewYWFW20081205 t3 where t3.bakfwid = t1.bakfwid)

(2) create a backup table

Create table Table_room081205

Select * from csywdk. table_room

(3) Replace the original table

Drop table sde. table_room

Create table sde. table_room

Select * from tmp_new_table_room081205

In this Q & amp; A; PL/SQL efficiency; is also mentioned:

"Tasks that can be processed with one statement must not be implemented by multiple programming statements ".

When I was always afraid of executing a sentence, the rollback segment was not big enough. It seems that I had to prepare the hard disk as the best strategy.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.