I have a table in oracle DB with 3 columns:
CREATE TABLE MB_TRANSACTIONS(
NAME VARCHAR2(100) NOT NULL,
CREATED_TS TIMESTAMP,
MESSAGE CLOB NOT NULL
);
Inserts will be happened on this table through java Prepared statement:
<sql-definition>
<sql>
INSERT INTO ${PACKAGE}.MB_TRANSACTIONS(NAME,CREATED_TS, MESSAGE)
VALUES(?,TO_DATE(?,'MM/DD/YYYY HH24:MI:SS'),?)
</sql>
<bindParams>
<param seqNo="1" name="NAME" type="Types.VARCHAR"/>
<param seqNo="2" name="CREATED_TS" type="Types.DATE"/>
<param seqNo="3" name="MESSAGE" type="Types.CLOB"/>
</bindParams>
</sql-definition>
When the message size is large(7MB) PreparedStatement.execute() takes too long to Insert when compared to smaller messages (1MB).
Large message time:
SQLStatement:execute() - Time took :31145ms
Smaller message:
SQLStatement:execute() - Time took :50ms
I am setting clob thru java code as follows:
case Types.CLOB:
PreparedStatement cs;
if(value != null || !"".equals(value)){
cs.setObject(paramPosition, value);
}
My Questions:
1. Is there a way to improve the performance of the CLOB Fields ?
2. Do we need to consider any parameters on database while creating table with CLOB column.
3. Do I need to change any parameters on tablespace to make that fast?
4. Do I am doing Insertion in a wrong way thru Java?