---------------------------------------------------------------
--搭建测试环境:
--查看索引是否存在于当前用户
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_INDEXNOLOGGING;
INSERT /*+APPEND*/INTO TEST_INDEX SELECT * FROM TEST_INDEX_DATA;
COMMIT;
ALTER INDEX IDX_TEST_INDEX REBUILD NOLOGGING;