oracle插入数据 [英] oracle inserting data

问题描述

我正在尝试将数据从oracle中的一个表复制到另一个表中
两个表具有不同的列名
我从一个表中的数据集中获取数据，然后插入另一张表中
共有28000条记录
加载需要6分钟
不允许这么多时间
我该如何最小化这段时间?

I m trying to copy data from one table in oracle to another
both tables have different column names
i take data in dataset from one table and insert in another table
there are 28000 records
it takes 6 minutes to load
this much time not allowed
how can i minimize this time?

推荐答案

写一个(如果需要的话，参数化的)存储过程以执行基于集合的插入并调用sp，而不是加载数据并执行一次插入一行；

Write a (parameterised if needed) stored procedure to perform a set based insert and call the sp rather than loading the data and doing a one row at a time insert;

INSERT INTO A
(
    COLUMN_A
    ,COLUMN_B
    ,COLUMN_C
    ,COLUMN_D
)
SELECT
    COLUMN_W
    ,COLUMN_X
    ,COLUMN_Y
    ,COLUMN_Z
FROM
    B
WHERE
    B.COLUMN_Z = @MyParameter;

这取决于列数，列的数据类型以及每行中数据的大小.但是通常，如果您在目标表中定义了索引，请禁用索引，插入行，然后重新启用索引.在批量更新期间，索引会更新，这会增加开销.

It depends on the number of columns, the data type of the columns and the size of data in each row. But generally, if you have defined indexes in your target table, disable the indexes, insert the rows and then re-enable the indexes. During bulk updates, indexes are updated which adds to the overhead.

这篇关于oracle插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！