有什么办法可以加快以下插入速度? [英] Is there any way I can speed up the following insert(s)?

问题描述

有什么方法可以加快这段代码的速度吗?

Is there any way of speeding up this piece of code?

FOR j IN 1..max
LOOP
  INSERT INTO myTable VALUES(DBMS_RANDOM.VALUE(1, 500), DBMS_RANDOM.VALUE(1, 500));
END LOOP;

该循环将至少发生一百万次，因此有超过一百万次插入.我知道FORALL，但是那是为了收集已经存在的数据..对我没有太大帮助.因此，我想知道是否有比经典循环更好的方法，或者至少可以做一些其他改进(例如，在创建表时添加/删除某些选项)，可以使此方法运行得更快的任何方法.

The loop is gonna happen for at least a million times, so a million+ inserts. I know about FORALL, but that is for collections of data that already exists.. not really helpful for me. So I was wondering if there's a better way than a classic loop, or at least some other improvements that I can do(like adding/removing some options when I create the table), anything that can make this run faster.

提前谢谢！

推荐答案

您可能想尝试使用单个插入语句，而不是执行一百万次语句:

You might want to try to use a single insert statement instead of executing a statement a million times:

insert into myTable
  select
    dbms_random.value(1, 500),
    dbms_random.value(1, 500)
  from
    dual
  connect by
    level <= 1*1000*1000;

附带说明:您是否测量了执行dbms_random所花费的时间以及实际上 插入值上花费了多少时间?

On a side note: did you measure how much time is spent on executing dbms_random and how much time on actually inserting the values?

这篇关于有什么办法可以加快以下插入速度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！