insert大量数据经验之谈

在很多时候，我们会需要对一个表进行插入大量的数据，并且希望在尽可能短的时间内完成该工作，这里，和大家分享下我平时在做大量数据insert的一些经验。 　　前提：在做insert数据之前，如果是非生产环境，请将表的索引和约束去掉，待insert完成后再建索引和约束。 　　1. 　　insert into tab1 select * from tab2; 　　commit; 　　这是基础的insert语句，我们把tab2表中的数据insert到tab1表中。根据经验，千万级的数据可在1小时内完成。但是该方法产生的arch会非常快，需要关注归档的产生量，及时启动备份软件，避免arc 在数据库管理中，高效地插入大量数据是一项关键任务，尤其对于大数据处理和分析而言。本文主要分享了在非生产环境中快速、大批量插入数据的一些经验技巧，这些技巧主要适用于Oracle数据库系统。 基础的`INSERT INTO ... SELECT FROM`语句是最常见的数据迁移方式。例如： ```sql INSERT INTO tab1 SELECT * FROM tab2; COMMIT; ``` 这种操作适用于百万至千万级别的数据，可以在一小时内完成。然而，这种方法会导致归档日志（ARCH）快速生成，因此需要密切关注归档空间并及时备份，防止磁盘空间不足。 为了优化性能，可以采用以下方法： 1. 使用`NOLOGGING`和`APPEND` Hint： ```sql ALTER TABLE tab1 NOLOGGING; INSERT /*+ APPEND */ INTO tab1 SELECT * FROM tab2; COMMIT; ALTER TABLE tab1 LOGGING; ``` 这种方法减少了归档日志的生成，加快了插入速度，但不适合在启用强制日志记录（FORCE LOGGING）的Data Guard环境中使用，因为这可能导致数据丢失风险。 2. 并行DML（Parallel DML）： ```sql ALTER SESSION ENABLE PARALLEL DML; INSERT /*+ PARALLEL */ INTO tab1 SELECT * FROM tab2; COMMIT; ``` 通过并行执行插入操作，可以显著提高效率，但并发度受到`parallel_max_servers`初始化参数的限制。同时，高并发可能导致enqueue等待，需谨慎使用。 3. 分区表并发插入： ```sql INSERT INTO tab1 PARTITION (p1) SELECT * FROM tab2; INSERT INTO tab1 PARTITION (p2) SELECT * FROM tab2; ... ``` 对于分区表，可以针对每个分区进行并发插入，增加处理速度。分区越多，可以并发的进程越多，但并发可能导致enqueue等待。 4. 批量绑定（Bulk Binding）： ```sql DECLARE TYPE dtarray IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; v_col1 dtarray; v_col2 dtarray; v_col3 dtarray; BEGIN SELECT col1, col2, col3 BULK COLLECT INTO v_col1, v_col2, v_col3 FROM tab2; FORALL i IN 1 .. v_col1.COUNT INSERT INTO tab1 WHERE tab1.col1 = v_col1(i); END; ``` 批量绑定可以减少PL/SQL和SQL引擎间的上下文切换，提高处理效率，特别适合在线处理场景，无需系统停机。 5. SQL*Plus的COPY命令： ```bash sqlplus -s user/pwd < runlog.txt SET COPYCOMMIT 2; SET ARRAYSIZE 5000; COPY FROM user/pwd@sid – TO user/pwd@sid – INSERT tab1 USING SELECT ... ``` SQL*Plus的COPY命令允许批量插入，通过调整`ARRAYSIZE`和`COPYCOMMIT`参数可以进一步优化性能。 这些技巧可以根据实际场景灵活应用，以提高插入大量数据的效率。在执行这些操作时，确保对生产环境的影响降到最低，并始终监控系统的资源使用情况，以防止潜在的问题。在使用过程中，应结合实际情况测试和比较不同方法的效果，以便选择最适合的策略。