请教下2个问题

onunix

Yong Huang 发表于 2013-4-28 22:30
So Oracle's direct path insert or load bypassing the SQL layer is completely irrelevant.

Not sure ...

谢谢Yong Huang，中间过了个五一节，回了趟老家，因此没有及时回复，答复一下： I assume the data you have in memory are in its own format, not data already in Oracle's PL/SQL arrays. How do you bulk insert?

我们打算使用数组插入，使用：
use the Oracle array interface and input many values with a single statement and a single call to OCIStmtExecute(). In this case you bind an array to an input placeholder, and the entire array can be passed at the same time, under the control of the iters parameter.

也就是说：

使用OCIBindByPos把内存数组（例如1000条记录）绑定，绑定完后，然后调用OCIStmtExecute() 进行一次性插入。（内存数据是以数组的形式存在的）。


详见相关说明：


OCI Array Interface

You can pass data to Oracle in various ways. You can execute a SQL statement repeatedly using the OCIStmtExecute() routine and supply different input values on each iteration. Alternatively, you can use the Oracle array interface and input many values with a single statement and a single call to OCIStmtExecute(). In this case you bind an array to an input placeholder, and the entire array can be passed at the same time, under the control of the iters parameter.

The array interface significantly reduces round-trips to Oracle when you need to update or insert a large volume of data. This reduction can lead to considerable performance gains in a busy client/server environment. For example, consider an application that needs to insert 10 rows into the database. Calling OCIStmtExecute() ten times with single values results in ten network round-trips to insert all the data. The same result is possible with a single call to OCIStmtExecute() using an input array, which involves only one network round-trip.

Note: When using the OCI array interface to perform inserts, row triggers in the database are fired as each row of the insert gets inserted.

cszxheap

从主观上“臆断”，通过oci进行数组批量插入的时间“应该”比落地格式文件后批量load的速度要快，当然，这是一个可以被实验的案例。

onunix

cszxheap 发表于 2013-5-9 10:25
从主观上“臆断”，通过oci进行数组批量插入的时间“应该”比落地格式文件后批量load的速度要快，当然，这是 ...

i think so too , i  am trying to test it,and i found a array insert sample in the $ORACLE_HOME  diretory , and i try to compile it ,but showed errors

D:\app\yu\product\11.2.0\dbhome_1\OCI\samples>bcmake.bat
.
Please set environment variable BORLAND_HOME