Here are my findings
- the jdbc thin driver WRITES the clob 4,000 bytes at a time. It reads it 32k at a time.
- the jdbc THICK (oci8) driver uses internal API calls and is not limited to 4,000 bytes during the write. It is many times faster.
- the long interface appears to work at about the same speed using either thick or thin.
- using the OCI8 jdbc driver, we can write clobs at about the same speed and read them as fast.
In any case, using DBMS_LOB.READ and WRITE instead of the helper functions will allow you to contol the "chunksize" explicitly. You can chunk the data upto 32k in size. See
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:110812348061 <code>for an example using Java. It shows writing a BLOB using 32,000 bytes as the chunksize.
I used the tables:
create table t1 ( id int, data clob );
create table t2 ( id int, data long );
in this test. My outcome was:
clob.jdbc.thick
JDBC driver version is 8.1.6.2.0
2001-01-22 09:45:18.378:Going to write
2001-01-22 09:45:24.626:Going to read ~6.3 seconds
2001-01-22 09:45:24.959:done ~.3 seconds
clob.jdbc.thin
JDBC driver version is 8.1.6.2.0
2001-01-22 09:43:38.04:Going to write
2001-01-22 09:44:17.577:Going to read ~39.5 seconds
2001-01-22 09:44:18.257:done ~.8 seconds
long.jdbc.thick
JDBC driver version is 8.1.6.2.0
2001-01-22 09:52:41.086:Going to write
2001-01-22 09:52:46.519:Going to read ~5.5 seconds
2001-01-22 09:52:47.257:done ~.7 seconds
long.jdbc.thin
JDBC driver version is 8.1.6.2.0
2001-01-22 09:53:53.426:Going to write
2001-01-22 09:53:58.906:Going to read ~5.5 seconds
2001-01-22 09:53:59.28:done ~.3 seconds
This was using a 1.4meg clob as a test.
I used this code:
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
class JDBCVersion
{
public static void showTimeStamp( String msg )
{
System.out.println( (new
Timestamp(System.currentTimeMillis())).toString() +
":" + msg );
}
public static void test_write(Connection conn) throws Exception
{
PreparedStatement pstat1 =
conn.prepareStatement
("insert into T1 values (?, empty_clob())");
PreparedStatement pstat2 =
conn.prepareStatement
("select DATA from T1 where id=? for update");
showTimeStamp( "Generating data" );
String outString = "Hello World";
for( int i = 1; i < 18; i++ )
outString = outString + outString;
showTimeStamp( "Generated " + outString.length() +
" bytes of data" );
showTimeStamp(" Inserting" );
pstat1.setInt(1, 1);
pstat1.executeUpdate();
showTimeStamp( "Inserted data...." );
pstat2.setInt(1, 1);
ResultSet res = pstat2.executeQuery();
showTimeStamp( "Executed Select" );
if (res.next())
{
showTimeStamp( "Fetched Row" );
CLOB clob = (CLOB)res.getClob(1);
showTimeStamp( "Got Clob" );
clob.putString(1, outString);
showTimeStamp( "put string" );
}
res.close();
conn.commit();
}
public static void test_read(Connection conn ) throws Exception
{
PreparedStatement pstat =
conn.prepareStatement("select DATA from T1 where id=?");
showTimeStamp( "Executing Query" );
pstat.setInt(1, 1);
ResultSet res = pstat.executeQuery();
while (res.next())
{
showTimeStamp( "Fetched row" );
Clob dataClob = res.getClob(1);
showTimeStamp( "got clob" );
String data =
dataClob.getSubString(1, (int)dataClob.length());
showTimeStamp(" got string " + ": length: " +
data.length() + "; data: " +
data.charAt(0) + data.charAt(1) + "..." +
data.charAt(data.length()-1));
}
res.close();
}
public static void main(String args[])throws Exception
{
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());
Connection conn=DriverManager.getConnection
//("jdbc:oracle:thin:@aria-dev:1521:ora816dev"
("jdbc:oracle:oci8:@ora816dev.us.oracle.com"
,"scott","tiger");
DatabaseMetaData meta=conn.getMetaData();
System.out.println
("JDBC driver version is "+meta.getDriverVersion());
conn.setAutoCommit(false);
Statement stmt = conn.createStatement ();
stmt.execute( "alter session set sql_trace=true" );
stmt.execute( "delete from t1" );
showTimeStamp( "Going to write" );
test_write(conn);
showTimeStamp( "Going to read" );
test_read(conn);
showTimeStamp( "done" );
}
}
import java.io.*;
import java.lang.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
class JDBCVersion2
{
public static void showTimeStamp( String msg )
{
System.out.println( (new
Timestamp(System.currentTimeMillis())).toString() +
":" + msg );
}
public static void test_write(Connection conn) throws Exception
{
PreparedStatement pstat =
conn.prepareStatement("insert into T2 values (?, ?)");
showTimeStamp( "Generating data" );
String outString = "Hello World";
for( int i = 1; i < 18; i++ )
outString = outString + outString;
showTimeStamp( "Generated " + outString.length() +
" bytes of data" );
int LENGTH = outString.length();
System.out.println(" Inserting " );
pstat.setInt(1, 1);
pstat.setCharacterStream
(2, new StringReader(outString.toString()), LENGTH);
showTimeStamp(" Inserting" );
pstat.executeUpdate();
showTimeStamp( "Inserted data...." );
conn.commit();
pstat.close();
}
public static void test_read(Connection conn ) throws Exception
{
PreparedStatement pstat = conn.prepareStatement
("select DATA from T2 where id=?");
pstat.setInt(1, 1);
showTimeStamp( "Executing Query" );
ResultSet res = pstat.executeQuery();
while (res.next()) {
showTimeStamp( "Fetched row" );
String data = res.getString(1);
showTimeStamp( "got long" );
//System.out.println(data + ": " + data.length());
showTimeStamp(" " + 1 + ": length: " +
data.length() + "; data: " +
data.charAt(0) +
data.charAt(1) + "..." +
data.charAt(data.length()-1));
}
res.close();
}
public static void main(String args[])throws Exception
{
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());
Connection conn=DriverManager.getConnection
("jdbc:oracle:thin:@aria-dev:1521:ora816dev"
//("jdbc:oracle:oci8:@ora816dev.us.oracle.com"
,"scott","tiger");
DatabaseMetaData meta=conn.getMetaData();
System.out.println("JDBC driver version is "+
meta.getDriverVersion());
conn.setAutoCommit(false);
Statement stmt = conn.createStatement ();
stmt.execute( "alter session set sql_trace=true" );
stmt.execute( "delete from t2" );
showTimeStamp( "Going to write" );
test_write(conn);
showTimeStamp( "Going to read" );
test_read(conn);
showTimeStamp( "done" );
}
}