RE: merge statement with clob field

From: David Boyd <davidb158_at_hotmail.com>
Date: Wed, 31 Oct 2007 13:31:25 -0400
Message-ID: <BAY137-W28824C5DF064BBA68E8DE6EF930@phx.gbl>

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_at_gmail.comTo: davidb158_at_hotmail.comSubject: Re: merge statement with clob fieldCC: oracle-l_at_freelists.org A couple of things :

Are you on Solaris 9/10?
Is the tablespace you're writing the CLOB to set to non ASSM? otherwise recreate it as such.
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_at_hotmail.com> wrote:

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

From: davidb158_at_hotmail.comTo: oracle-l_at_freelists.orgSubject: 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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 31 2007 - 12:31:25 CDT