oracle 批量插入方法性能比较union all更好

柔光的暖阳◎ 2022-12-22 08:54 85阅读 0赞

先讲点真实的故事吧。

我们项目用的oracle11g，项目还是用swing界面展示数据的，之前数据量没上来的时候用下面的批量删除和插入都没问题（自己测试 的时候量级是个位数到百位数之间）

批量删除sql示例

<delete id="deleteSignalTypes">
            delete from IDS_SIGNAL_TYPE WHERE ID in
            <foreach collection="signalTypeIds" item="signalTypeId" index="index" open="(" close=")" separator=",">
                #{signalTypeId,jdbcType=VARCHAR}
            </foreach>
        </delete>

条件查询sql示例

<select id="queryPinRelaByIds" parameterType="string" resultMap="pinRelaTempMap">
            SELECT * FROM IDS_PIN_RELA
            WHERE PIN_ID IN
            <foreach collection="ids" item="id" open="(" close=")" separator=",">
                #{id,jdbcType=VARCHAR}
            </foreach>
            OR
            TO_PIN_ID IN
            <foreach collection="ids" item="id" open="(" close=")" separator=",">
                #{id,jdbcType=VARCHAR}
            </foreach>
        </select>

批量插入sql示例

<insert id="batchInsertConnector">
            <foreach collection="connectors" item="connector" index="index" separator=";" open="begin" close=";end;">
                INSERT INTO IDS_CONNECTOR
                (ID,
                DEVICE_ID,           
                CONN_TYPE)
                VALUES
                (
                #{connector.id,jdbcType=VARCHAR},
                #{connector.deviceId,jdbcType=VARCHAR},          
                #{connector.connType,jdbcType=VARCHAR}
                )
            </foreach>
        </insert>

效率在数据量大的时候插入容易把数据库搞崩，而且oracle一次最多执行1000条记录，再多就卡死oracle了。

后来用了下面的插入方式就可以了，不卡死数据库，而且效率也比较高

<insert id="batchInsertDevices" parameterType="com.esoon.ids.model.Device">
            insert into IDS_DEVICE
            (ID, PROJECT_ID, CODE, FILE_LIST_ID)
            <foreach collection="devices" item="device" separator="UNION ALL">
                SELECT #{device.id,jdbcType=VARCHAR},
                #{device.projectId,jdbcType=VARCHAR},
                #{device.code,jdbcType=VARCHAR},
                #{device.fileListId,jdbcType=VARCHAR}
                FROM dual
            </foreach>
        </insert>