如何提高Oracle insert效率

oracle index 大量数据插入效率问题

–搭建测试环境：

–查看索引是否存在于当前用户

select * from dba_indexes where owner=‘system’ and index_name=‘IDX_TEST_INDEX’;

DROP TABLE TEST_INDEX;

CREATE TABLE TEST_INDEX

(

USERID NUMBER,

USERNAME VARCHAR2(32),

CREATEDATE DATE

);

DROP TABLE TEST_INDEX_DATA;

CREATE TABLE TEST_INDEX_DATA AS SELECT * FROM TEST_INDEX;

BEGIN

FOR I IN 1 … 2000000 LOOP

INSERT INTO TEST_INDEX_DATA VALUES (I, '测试索引_' || I, SYSDATE);

END LOOP;

COMMIT;

END;

SELECT COUNT(1) FROM TEST_INDEX_DATA;

SELECT COUNT(1) FROM TEST_INDEX;

---

–没有索引的时候，直接插入数据

TRUNCATE TABLE TEST_INDEX;–0.078

INSERT INTO TEST_INDEX SELECT * FROM TEST_INDEX_DATA;–6.25

COMMIT;–0.281

–创建一个索引，然后直接插入数据

TRUNCATE TABLE TEST_INDEX;–2.156

CREATE INDEX IDX_TEST_INDEX ON TEST_INDEX(USERID,USERNAME);–0.125

INSERT INTO TEST_INDEX SELECT * FROM TEST_INDEX_DATA;–42.688

COMMIT;–0.079

---

----下面针对有索引的表，在进行大量数据插入的时候测试对比。----

---

–方案1：先unusable，再truncate，再insert，再rebuild.

ALTER INDEX IDX_TEST_INDEX UNUSABLE;–0.86

TRUNCATE TABLE TEST_INDEX;–2.172

INSERT INTO TEST_INDEX SELECT * FROM TEST_INDEX_DATA;–43.391

COMMIT;–0.109

ALTER INDEX IDX_TEST_INDEX REBUILD;–17.328

–方案2：先truncate，再unusable，再insert，再rebuild.

TRUNCATE TABLE TEST_INDEX;–1.313

ALTER INDEX IDX_TEST_INDEX UNUSABLE;–0.765

INSERT INTO TEST_INDEX SELECT * FROM TEST_INDEX_DATA;–8.516

COMMIT;–0.265

ALTER INDEX IDX_TEST_INDEX REBUILD;–10.328

---

---

–方案3：先drop索引，再truncate，在insert，在create索引

DROP INDEX IDX_TEST_INDEX;–0.156

TRUNCATE TABLE TEST_INDEX;–1.735

INSERT INTO TEST_INDEX SELECT * FROM TEST_INDEX_DATA;–6.188

COMMIT;–0.5

CREATE INDEX IDX_TEST_INDEX ON TEST_INDEX(USERID,USERNAME);–10.812

–方案4：先truncate，再drop索引，在insert，在create索引

TRUNCATE TABLE TEST_INDEX;–2.25

DROP INDEX IDX_TEST_INDEX;–0.109

INSERT INTO TEST_INDEX SELECT * FROM TEST_INDEX_DATA;–6.157

COMMIT;–0.188

CREATE INDEX IDX_TEST_INDEX ON TEST_INDEX(USERID,USERNAME);–11.078

---

–删除测试环境：

DROP TABLE TEST_INDEX;

DROP TABLE TEST_INDEX_DATA;

---

/*综上所述：

对于有索引的表，在插入大量数据的时候，想要提高性能的话，可以有两个方法：

1、在插入数据之前，需要将索引给dorp掉，然后等数据插入完成以后，在create上。

2、在插入数据之前，需要将索引给禁用掉，然后等数据插入完成以后，在重新创建上。

需要注意的地方是：

如果你在插入数据之前，需要truncate这个表，然后在插入，那么你对索引禁用的操作一定要放在truncate表语句之后，

如果放在truncate表之前，那么等于你对索引没有进行任何操作。效率仍然还是那样慢。

建议使用上述的方案2或3最佳。

如果效率仍然很低，可以考虑使用hint语法。

insert /+append/ into TEST_INDEX select * from TEST_INDEX_DATA;

在向TEST_INDEX表中插入数据之前，还可以将表TEST_INDEX修改为nologging，这样插入数据的时候效率会高一些。

alter table TEST_INDEX nologging.

在创建索引或重建索引的时候，还可以指定nologging，这样在重建索引的时候，效率会高一些。

alter index IDX_TEST_INDEX rebuild nologging;

不管你的数据运行模式是归档日志模式，还会非归档日志模式，使用nologging都会提高插入数据的效率。

*/

结合以上个点，最后的实现思路如下：

TRUNCATE TABLE TEST_INDEX;–5.406

ALTER INDEX IDX_TEST_INDEX UNUSABLE;

ALTER TABLE TEST_INDEX NOLOGGING;

INSERT /+APPEND/INTO TEST_INDEX SELECT * FROM TEST_INDEX_DATA;

COMMIT;

ALTER INDEX IDX_TEST_INDEX REBUILD NOLOGGING;