Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: merge statement with clob field

Re: merge statement with clob field

From: Finn Jorgensen <finn.oracledba_at_gmail.com>
Date: Fri, 26 Oct 2007 17:14:54 -0400
Message-ID: <74f79c6b0710261414u7417d9bby61000447b195a58c@mail.gmail.com>


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_at_hotmail.com> wrote:
>
>
> We're in Oracle 10.2.0.3. Any input will be highly appreciated.
>
> David
>
> ------------------------------
>
> From: davidb158_at_hotmail.com
> To: oracle-l_at_freelists.org
> Subject: merge statement with clob field
> Date: 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!<http://www.reallivemoms.com/?ocid=TXT_TAGHM&loc=us>
>
>
> ------------------------------
> Climb to the top of the charts! Play Star Shuffle: the word scramble
> challenge with star power. Play Now!<http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_oct>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 26 2007 - 16:14:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US