How to efficiently insert data into index-rich oracle db?

How to efficiently insert data into index-rich oracle db?

妖妓 发布于 2021-12-01 字数 230 浏览 769 回复 6 原文

How do we insert data about 2 million rows into a oracle database table where we have many indexes on it?
I know that one option is disabling index and then inserting the data. Can anyone tell me what r the other options?

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。



需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。


可爱咩 2022-06-07 6 楼

As an another option, one can use oracle advanced and faster data pump (expdp, impdp) utilities availability 10 G onward. Though, Oracle still supports old export/import (exp, imp).

Oracle provides us with many choices for data loading, some way faster than others:

  • Oracle10 Data Pump Oracle import utility
  • SQL insert and merge
  • statements PL/SQL bulk loads for the forall PL/SQL operator
  • SQL*Loader

The pros/cons of each can be found here ..

段念尘 2022-06-07 5 楼

If you are sure about the data, besides the index you can disable referential and constraint checks. You can also lower the transaction isolation level.

All these options come with a price, though. Each option increases your risk of having corrupt data in the sense that you may end up with null FK's etc.

摘星┃星的人 2022-06-07 4 楼

Not sure how you are inserting the records; if you can; insert the data in smaller chunks. In my experience 50 sets of 20k records is often quicker than 1 x 1000000

Make sure your database files are large enough before you start save you from database growth during the insert ...

誰ツ都不明白 2022-06-07 3 楼

there are many tricks to fasten de insert, below i wrote some of them

  • if you use sequence.nextval for insert make sure sequence has big cache value (1000 is enough usually)
  • drop indexes before insert and create afterwards (make sure you get the create scripts of indexes before dropping) while creating you can use parallel option
  • if target table has fk dependencies disable them before insert and after insert enable again. if you are sure of your data you can use novalidate option (novalidate option is valid for oracle, other rdbms systems probably have similar option)
  • if you select and insert you can give parallel hint for select statement and for insert you can use append hint (direct-path insert ) (direct-path insert concept is valid for oracle, other rdbms systems probably have similar option)
那些过往 2022-06-07 2 楼

Check SQL*Loader out (especially the paragraph about performance optimization) : it is the standard bulk loading utility for Oracle, and it does a good job once you know how to use it (as always with Oracle).

日裸衫吸 2022-06-07 1 楼

bulk load with presorted data in index key order