Fastest way to load CLOB
YasuDec 17 2010 — edited Dec 19 2010Hi All,
We need to improve loading of data using below insert statement.
This above insert is performed from procedure, and it inserts about 250 rows/min into payload1 table which is having CLOB datatype.
We need to improve loading from 250 to 1000 rows/min....
We cannot use append hint as other sessions will performing DML on this table during load. Also we cannot use nologging for table and lob as we need to rebuild standby repeatedly, because loading is not one time activity.
Doubts:
How can we increase loading?
Is it best to use BULK COLLECT INTO, FORALL INSERT ? If yes, how to do it?
Is it best to use array processing ? If yes how to do it?
Are there any other methods to load data into clob faster way ?
Is it better to change datatype from CLOB to XMLTYPE, as documents are framed into XML fashion using functions xmlelement and xmlforest ?
Oracle Version : 10.2.0.4
payload table structure:
SQL> desc USR.PayLoad1
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
IFQUEUEID NOT NULL NUMBER(19)
PAYLOADID NOT NULL NUMBER(5)
PAYLOADXML CLOB
Edited by: Yasu on Dec 19, 2010 3:33 PM