RE: merge statement with clob field

Finn,
 
Thanks for your advice.  
 
We're on Solaris 10.  Our lob tablespace is non ASSM.  I tried to change 
filesystemio_options and it did not help.
 
Finally an index on ID helps and the merge finishes in 40 secs. David


Date: Fri, 26 Oct 2007 17:14:54 -0400From: finn.oracledba@xxxxxxxxxxx: 
davidb158@xxxxxxxxxxxxxxxxxx: Re: merge statement with clob fieldCC: 
oracle-l@xxxxxxxxxxxxx
A couple of things :
 
1) Are you on Solaris 9/10?
2) Is the tablespace you're writing the CLOB to set to non ASSM? otherwise 
recreate it as such.
3) Try setting the init.ora parameter filesystemio_options=setall. If that 
doesn't improve, try setting it to directio.
 
Finn 
On 10/26/07, David Boyd <davidb158@xxxxxxxxxxx> wrote: 

We're in Oracle 10.2.0.3.  Any input will be highly appreciated. David

From: davidb158@xxxxxxxxxxxxx: oracle-l@xxxxxxxxxxxxxxxxxxxx: merge statement 
with clob fieldDate: Fri, 26 Oct 2007 11:42:25 -040 Hi list,  

Does any one have experience using merge statement with clob?  We are trying to 
load a table from an external table using merge statement on the daily basis.  
Here is table definition: 
 
create table test
(id number,
position varchar2 (10),
desc clob
);
 
create table test_xt
(id number,
position varchar2 (10),
desc clob
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY xt_dir
ACCESS PARAMETERS 
(records delimited by newline skip 1
CHARACTERSET US7ASCII
BADFILE 'test_xt.bad'
LOGFILE 'test_xt.log' 
fields terminated by ',' OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(id,
position,
desc varchar2(32000 )
)
)
LOCATION ('test.csv')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
 
There are 98000 records in the test_xt table.  The merge statement finishes in 
7 secs when I change the clob to varchar2(4000).  With the clob, it seems never 
finished.  I let the merge statement run for more than 3 hours and killed it.  
I have CACHE set in test table for clob.  Is there any way to improve the 
performance? 
 
Another question is how to load multiple line data from csv file into external 
table, e.g. one record in csv file is as following: 
 
2, "DBA", "this position requires 
a certificate of OCP and 
10 years experience in Oracle."
 
This record can not be loaded into external table.  I changed "records 
delimited by newline" to 'records delimited by '|'" and modified the csv file.  
It still does not work. I know I can use the LOBFILE method, but exporting the 
clob data to a file for each record is not an option. 
 
David

Peek-a-boo FREE Tricks & Treats for You! Get 'em! 

Climb to the top of the charts!  Play Star Shuffle:  the word scramble 
challenge with star power. Play Now!
_________________________________________________________________
Windows Live Hotmail and Microsoft Office Outlook – together at last.  Get it 
now.
http://office.microsoft.com/en-us/outlook/HA102225181033.aspx?pid=CL100626971033

Other related posts: