经过长时间接触Oracle Insert和bulk Insert,我对比了一下他们的执行效率。在这里和大家分享一下,希望你看完本文后有不少收获。
测试java的insert 同使用9i以后的bulk Insert 的速度,结果显示通过bulk Insert 速度相当的快.
100000条记录
insert ,---------------93秒
bulk Insert -------------0.441秒
环境:
oracle 10.2.0.3 Windows 2000Server
java
代码:
- SQL> desc a
- Name Type Nullable Default Comments
- ---- ------------ -------- ------- --------
- ID INTEGER Y
- NAME VARCHAR2(20) Y
bulk Insert 使用的类型及过程
- create or replace type i_table is table of number(10);
- create or replace type v_table is table of varchar2(10);
- create or replace procedure pro_forall_insert(v_1 i_table,v_2 v_table)
- as
- c integer;
- begin
- forall i in 1.. v_1.count
- insert into a values(v_1(i),v_2(i));
- end;
测试的java代码:
- import java.io.*;
- import java.sql.*;
- import java.util.*;
- import javax.naming.Context;
- import javax.naming.InitialContext;
- import javax.naming.*;
- import oracle.jdbc.OracleTypes;
- import oracle.sql.*;
- import oracle.sql.ARRAY;
- import oracle.sql.ArrayDescriptor;
- import oracle.sql.STRUCT;
- import oracle.sql.StructDescriptor;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import oracle.jdbc.OracleCallableStatement;
- public class testOracle {
- public testOracle() {
- Connection oraCon = null;
- PreparedStatement ps = null;
- Statement st = null;
- ResultSet rs = null;
- try {
- try {
- Class.forName("oracle.jdbc.driver.OracleDriver");
- } catch (ClassNotFoundException ex) {}
- oraCon = DriverManager.getConnection("jdbc:oracle:thin:@192.168.15.234:1521:ora10g", "imcs","imcs");
- oraCon.setAutoCommit(false);
- } catch (SQLException ex) {
- ex.printStackTrace();
- }
- CallableStatement cstmt = null;
- oracle.sql.ArrayDescriptor a = null;
- oracle.sql.ArrayDescriptor b = null;
- if (1 == 1 )
- {
- Object[] s1 = new Object[100000];
- Object[] s2 = new Object[100000];
- for (int i = 0; i < 100000; i++) {
- s1[i] = new Integer(1);
- s2[i] = new String("aaa").concat(String.valueOf(i));
- }
- try {
- a = oracle.sql.ArrayDescriptor.createDescriptor("I_TABLE", oraCon);
- b = oracle.sql.ArrayDescriptor.createDescriptor("V_TABLE", oraCon);
- ARRAY a_test = new ARRAY(a, oraCon, s1);
- ARRAY b_test = new ARRAY(b, oraCon, s2);
- cstmt = oraCon.prepareCall("{ call pro_forall_insert(?,?) }");
- cstmt.setObject(1, a_test);
- cstmt.setObject(2, b_test);
- long aaaa = System.currentTimeMillis();
- System.out.println(System.currentTimeMillis());
- cstmt.execute();
- oraCon.commit();
- System.out.println(System.currentTimeMillis()-aaaa);
- catch (Exception e) {
- e.printStackTrace();
- }
- }
- else
- {
- try
- {
- PreparedStatement oraPs = null;
- String oraInsertSql =
- "insert into a values(?,?)";
- oraPs = oraCon.prepareStatement(oraInsertSql);
- long aaaa = System.currentTimeMillis();
- System.out.println(System.currentTimeMillis());
- for (int i = 0; i < 100000; i++)
- {
- oraPs.setInt(1,i);
- oraPs.setString(2, new String("aaa").concat(String.valueOf(i)));
- oraPs.executeUpdate();
- }
- oraCon.commit();
- System.out.println(System.currentTimeMillis()-aaaa);
- }
- catch (SQLException ex)
- {
- System.out.print("dddddd");
- System.out.print(ex.getMessage());
- }
- }
- try {
- jbInit();
- } catch (Exception ex) {
- ex.printStackTrace();
- }
- }
- public static void main(String args[]) {
- testOracle a = new testOracle();
- }
- private void jbInit() throws Exception {
- }
- };
【编辑推荐】