在 Oracle 中通过脚本更快地插入的方法？

我正在使用 C# .NET 4.0 应用程序，该应用程序使用 ODP.NET 11.2.0.2.0 和 Oracle 11g 数据库。该应用程序预加载了一些包含数据的查找表，并且由于大多数表的记录少于 20 条，因此脚本运行得非常快。然而，其中一个脚本有 802 条记录，插入记录需要 248.671 秒，对于一个提倡快速操作大量数据的数据库来说，这对于如此少量的数据来说似乎有点过分了。

所以我想知道，是否有比当前编写脚本的方式更快的方式通过脚本插入数据？

插入的表定义如下：

CREATE TABLE FileIds
(
     Id                 NUMERIC(38)                         NOT NULL
    ,Name               NVARCHAR2(50)   DEFAULT 'Unknown'   NOT NULL 
    ,FileTypeGroupId    NUMERIC(38)                         NOT NULL
    ,CONSTRAINT FK_FileIds_FileTypeGroups FOREIGN KEY ( FileTypeGroupId ) REFERENCES FileTypeGroups ( Id )
)

要插入的脚本如下所示：

BEGIN
    INSERT ALL
        INTO FileIds ( Id, FileTypeGroupId ) VALUES (1152,5)
        INTO FileIds ( Id, FileTypeGroupId ) VALUES (1197,10)
        INTO FileIds ( Id, FileTypeGroupId ) VALUES (1200,6)
        INTO FileIds ( Id, FileTypeGroupId ) VALUES (1143,3)
        INTO FileIds ( Id, FileTypeGroupId ) VALUES (1189,9)
        INTO FileIds ( Id, FileTypeGroupId ) VALUES (1109,7)
        INTO FileIds ( Id, FileTypeGroupId ) VALUES (1166,4)
        INTO FileIds ( Id, FileTypeGroupId ) VALUES (0,8)
        INTO FileIds ( Id, FileTypeGroupId ) VALUES (1149,2)
        INTO FileIds ( Id, FileTypeGroupId ) VALUES (1400,1)
        INTO FileIds ( Id, FileTypeGroupId ) VALUES (1330,11)
        INTO FileIds ( Id, FileTypeGroupId ) VALUES (1000,0)
        -- 790 Records removed for example purposes.
        SELECT * FROM DUAL;
    COMMIT;
END;

外键中引用的 FileTypeGroups 表是在加载 FileIds 表之前预先加载的。没有与 FileIds 表关联的序列或触发器，并且尚未为该表创建索引。

Problem

对于某些类型的语句，解析时间可能会呈指数增长，尤其是INSERT ALL。例如：

--Clear any cached statements, so we can consistently reproduce the problem.
alter system flush shared_pool;
alter session set sql_trace = true;

--100 rows
INSERT ALL
    INTO FileIds(Id,FileTypeGroupId) VALUES(1, 1)
    ...
    repeat 100 times
    ...
select * from dual;

--500 rows
INSERT ALL
    INTO FileIds(Id,FileTypeGroupId) VALUES(1, 1)
    ...
    repeat 500 times
    ...
select * from dual;

alter session set sql_trace = false;

通过 tkprof 运行跟踪文件，您可以看到大量行的解析时间急剧增加。例如：

100 行：

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.05          0          1          0           0
Execute      1      0.00       0.00          0        100        303         100
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.06       0.05          0        101        303         100

500 行：

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1     14.72      14.55          0          0          0           0
Execute      1      0.01       0.02          0        502       1518         500
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     14.74      14.58          0        502       1518         500

解决方案

1. 将大语句分成几个较小的语句。找到最佳尺寸很困难。在某些版本的 Oracle 上，存在大量行数会导致问题。我通常选择大约 100 行 - 足以获得分组语句的大部分好处，但又足够低以避免解析错误。或者...
2. Try the insert into ... select ... from dual union all ...方法代替。它通常运行得更快，尽管它的解析性能也可能随着大小而显着降低。
3. 升级甲骨文。新版本中的解析性能有所提高。在 12.2 版本中我无法再重现此问题。

Warning

不要从中吸取错误的教训。如果您担心 SQL 性能，99% 的情况下，您最好将相似的事物组合在一起，而不是将它们分开。你正在以正确的方式做事，你只是遇到了一个奇怪的错误。 （我搜索了 My Oracle Support，但找不到这方面的官方错误。）