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

Question

我正在使用 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 表相关联的序列或触发器，并且尚未为该表创建索引。

Answer 1

问题

对于某些类型的语句，解析时间可能会呈指数增长，尤其是 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

解决方案

把你的大陈述分解成几个较小的陈述。很难找到最佳尺寸。在某些版本的 Oracle 上，有很多行会导致问题。我通常使用大约 100 行 - 足以获得分组语句的大部分好处，但足够低以避免解析错误。或...

试试 insert into ... select ... from dual union all ...方法代替。它通常运行得更快，尽管它的解析性能也可能随着大小而显着降低。

升级甲骨文。新版本的解析性能有所提高。我无法再在 12.2 版中重现此问题。

警告

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