oracle 批量插入

有没有insert into的优化方法，我用多表关联select出来的数据有五十多万条，一次性插入到一张表中的效率太差，最好有代码举例
我的核心语句是insert into tab1 select Col1，col2，col3.…from tab2 left join tab3 left join
查询出来的表有五十多万行每天，每次单独查询的时候很快，一用insert into就卡死，拆分后也不管用
以下是执行计划

![img](https://i/

批量插入，例如，使用MySQL

INSERT INTO target_table (column1, column2, column3)
SELECT column1, column2, column3
FROM source_table1
JOIN source_table2 ON ...
JOIN source_table3 ON ...
LIMIT 10000;

然后，你可以通过循环或定时任务来重复执行此查询，直到所有数据都入。
事务处理：使用事务将多个插入操作组合在一起。这样可以减少提交事务时的开销。例如，使用MySQL：


START TRANSACTION;

-- 执行多个插入语句
INSERT INTO target_table (column1, column2, column3)
SELECT column1, column2, column3
FROM source_table1
JOIN source_table2 ON ...
JOIN source_table3 ON ...
LIMIT 10000;

-- 更多插入语句...

COMMIT;

关闭索引和约束：在进行大量数据插入之前，可以考虑关闭目标表的索引和约束。这样可以加速插入过程。但是，在插入完成后，别忘了重新启用它们，并检查数据的完整性。
使用加载数据文件代码

-- 导出查询结果到CSV文件
SELECT column1, column2, column3
FROM source_table1
JOIN source_table2 ON ...
JOIN source_table3 ON ...
INTO OUTFILE '/path/to/your/csvfile.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 使用LOAD DATA INFILE将数据导入到目标表中
LOAD DATA INFILE '/path/to/your/csvfile.csv'
INTO TABLE target_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(column1, column2, column3);

注意：这需要MySQL服务器和客户端之间的文件系统访问权限。并且，这种方法可能不适用于所有数据库系统。

发一下你的insert语句的执行计划和语句执行期间的等待事件看看。
insert执行慢通常是表有主键或唯一索引，关联查询部分慢只能看具体执行计划了。

INSERT ALL语句

INSERT ALL
INTO table1 (column1, column2) VALUES ('value1', 'value2')
INTO table1 (column1, column2) VALUES ('value3', 'value4')
INTO table1 (column1, column2) VALUES ('value5', 'value6')
...
SELECT 1 FROM DUAL;

INSERT INTO ... SELECT语句

INSERT INTO table1 (column1, column2)
SELECT column1, column2 FROM table2
WHERE ...

在mysql中，批量插入可以这么写：

insert into my_table(field_1,field_2)
values
(value_1,value_2),
(value_1,value_2),
(value_1,value_2);

oracle中不支持这么写，那在Oracle中，怎么通过一个insert语句批量插入数据呢？


```bash
INSERT ALL 
INTO my_table(field_1,field_2) VALUES (value_1,value_2) 
INTO my_table(field_1,field_2) VALUES (value_3,value_4) 
INTO my_table(field_1,field_2) VALUES (value_5,value_6)
SELECT 1 FROM DUAL;

**insert all into并不表示一个表中插入多条记录，而是表示多表插入各一条记录，而这多表可以是同一个表，就成了单表插入多条记录。根据后面子查询的结果，前面每条into语句执行一次，博客正文中value都是“字面量”，所以用select 1 from dual返回一条记录即可。**

这里有一个多条记录插入的模板您可以试一试

insert all
    into <tableName>[(<table_column1>,<table_column2>...)] 
    values([<column_value1>,<column_value2>...]) 
    [into <tableName>[(<table_column1>,<table_column2>...)] 
    values([<column_value1>,<column_value2>...])]...
select  <table_value1>[,<table_value2>...] from dual;

针对这种情况，可以考虑以下优化方法：

分批插入：将查询结果分批插入到目标表中，每次插入一部分数据。可以使用游标或者分页查询的方式。例如：

DECLARE @batch_size INT = 1000;
DECLARE @offset INT = 0;
WHILE @batch_size > 0
BEGIN
    INSERT INTO tab1 (Col1, Col2, Col3)
    SELECT Col1, Col2, Col3
    FROM (
        SELECT Col1, Col2, Col3, ROW_NUMBER() OVER (ORDER BY Col1) AS row_num
        FROM tab2 LEFT JOIN tab3 ON tab2.id = tab3.tab2_id
    ) AS t
    WHERE t.row_num > @offset AND t.row_num <= @offset + @batch_size;
    SET @batch_size = @@ROWCOUNT;
    SET @offset = @offset + @batch_size;
END

使用临时表：将查询结果先插入到临时表中，然后再将临时表中的数据插入到目标表中。例如：

SELECT Col1, Col2, Col3
INTO #temp_table
FROM tab2 LEFT JOIN tab3 ON tab2.id = tab3.tab2_id;

INSERT INTO tab1 (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM #temp_table;

DROP TABLE #temp_table;

禁用索引：在插入数据时，禁用目标表的索引，插入完成后再重新启用索引。例如：

ALTER INDEX ALL ON tab1 DISABLE;

-- 插入数据的语句

ALTER INDEX ALL ON tab1 REBUILD;

这样可以减少插入数据时的索引维护开销，提高插入效率。

需要注意的是，对于大量数据的插入操作，可能会对数据库的性能产生较大影响，建议在非高峰期进行操作。同时，应该根据实际情况进行调整，选择最适合当前场景的优化方法。

看一下插入的表是否有索引，如果有索引可以先删除索引，再插入数据，最后再重建索引，有索引情况下插入大量数据效率会很低。

INSERT
INTO table1 (column1, column2) VALUES ('value1', 'value2')
INTO table1 (column1, column2) VALUES ('value3', 'value4')
INTO table1 (column1, column2) VALUES ('value5', 'value6')
...
SELECT 1 FROM DUAL;

建议分批插入一次插入1000条循环插入

建议通过代码的方式，先查询出来，然后采用insert all的方法加上线程池的方式来优化。批量插入数据太大会报错，代码中就可以在分批。比如分X批，每批300条，mapper.xml的写法如下（实际代码）

<insert id="batchSave" parameterType="java.util.List">
        insert all
        <foreach collection="list" item="item" separator=" ">
            INTO cj_msg(
            id,type,type_id,title,detail,user_id,login_name,
            status,create_by,create_date,update_by,update_date,remark,del_flag)
            values
            (#{item.id},#{item.type},#{item.typeId},#{item.title},#{item.detail},#{item.user.id},#{item.loginName},
            #{item.status},#{item.createBy.id},#{item.createDate},#{item.updateBy.id},#{item.updateDate},#{item.remark},#{item.delFlag})
        </foreach>
        select ${list.size} from dual
    </insert>

针对大数据量的插入操作，可以采用以下一些优化方法：

1.使用分批插入：将插入操作分成多个批次，每个批次插入一部分数据，可以降低单次插入的数据量，减少锁冲突，提高插入效率。例如：


DECLARE @batch_size INT = 1000; -- 每次插入的数据量
DECLARE @offset INT = 0; -- 当前数据偏移量
WHILE 1=1
BEGIN
  INSERT INTO tab1 (col1, col2, col3)
  SELECT col1, col2, col3
  FROM tab2
  LEFT JOIN tab3 ON tab2.id = tab3.id
  ORDER BY tab2.id
  OFFSET @offset ROWS
  FETCH NEXT @batch_size ROWS ONLY;

  SET @offset = @offset + @batch_size;
  IF @@ROWCOUNT < @batch_size
    BREAK;
END

上述代码使用循环和OFFSET-FETCH语句来分批插入数据，每次插入@batch_size条数据，直到插入完毕。

2.关闭日志记录：插入操作会产生大量的日志记录，占用大量磁盘空间和系统资源，可以通过关闭日志记录来提高插入效率。例如：

-- 关闭日志记录
ALTER TABLE tab1 NOLOGGING;

-- 执行插入操作
INSERT INTO tab1 (col1, col2, col3)
SELECT col1, col2, col3
FROM tab2
LEFT JOIN tab3 ON tab2.id = tab3.id;

-- 开启日志记录
ALTER TABLE tab1 LOGGING;

上述代码使用ALTER TABLE语句关闭日志记录，执行插入操作后再重新开启日志记录。

3.使用临时表：将查询结果插入到临时表中，然后再将临时表中的数据插入到目标表中，可以减少对目标表的锁定和影响，提高插入效率。例如：

-- 创建临时表
CREATE TABLE #temp_tab1 (
  col1 INT,
  col2 VARCHAR(50),
  col3 DATETIME
);

-- 插入数据到临时表
INSERT INTO #temp_tab1 (col1, col2, col3)
SELECT col1, col2, col3
FROM tab2
LEFT JOIN tab3 ON tab2.id = tab3.id;

-- 插入数据到目标表
INSERT INTO tab1 (col1, col2, col3)
SELECT col1, col2, col3
FROM #temp_tab1;

-- 删除临时表
DROP TABLE #temp_tab1;

上述代码将查询结果插入到临时表#temp_tab1中，然后再将临时表中的数据插入到目标表tab1中。

需要注意的是，以上优化方法需要根据实际情况和系统环境进行调整和测试，并在操作前备份数据以防数据丢失或损坏。同时，对于大数据量的插入操作，还需要注意一些系统参数的设置，如I/O、内存和CPU等方面的优化，以提高插入效率。

问题描述：
该用户使用多表关联查询出来的数据有五十多万条，一次性插入到一张表中的效率太差，最好有代码举例。核心语句是insert into tab1 select Col1，col2，col3.…from tab2 left join tab3 left join。查询出来的表有五十多万行每天，每次单独查询的时候很快，一用insert into就卡死，拆分后也不管用。
分析：
1. 数据量过大导致插入效率低下
由于一次性插入五十多万条数据，数据量过大，导致插入效率低下。可以考虑分批次插入，每次插入一定数量的数据，减少单次插入的数据量，提高插入效率。
2. 索引对插入效率的影响
插入数据时，如果表中存在索引，插入数据时需要更新索引，会影响插入效率。可以考虑在插入数据前，先将索引禁用，插入完成后再启用索引。
3. 数据库参数对插入效率的影响
数据库参数也会影响插入效率，可以通过修改数据库参数来提高插入效率。例如，可以将数据库的UNDO_RETENTION参数设置为较小的值，减少回滚段的大小，提高插入效率。
4. 使用批量插入语句
可以使用批量插入语句，一次性插入多条数据，提高插入效率。例如，可以使用以下语句：
INSERT ALL
INTO tab1 (col1, col2, col3) VALUES (val1, val2, val3)
INTO tab1 (col1, col2, col3) VALUES (val4, val5, val6)
INTO tab1 (col1, col2, col3) VALUES (val7, val8, val9)
SELECT 1 FROM DUAL;
以上语句可以一次性插入多条数据，提高插入效率。
5. 使用并行插入
可以使用并行插入，将插入任务分配给多个进程同时执行，提高插入效率。可以通过修改数据库参数来启用并行插入。
代码示例：
以下是使用批量插入语句的示例代码：
INSERT ALL
INTO tab1 (col1, col2, col3) VALUES (val1, val2, val3)
INTO tab1 (col1, col2, col3) VALUES (val4, val5, val6)
INTO tab1 (col1, col2, col3) VALUES (val7, val8, val9)
SELECT 1 FROM DUAL;
以上代码可以一次性插入多条数据，提高插入效率。

你的插入没问题的,就是要多线程分批查入,每次插个一千条,多线程线程池处理,可以试试这个思路,