Re: merge statement with clob field
- From: "Finn Jorgensen" <finn.oracledba@xxxxxxxxx>
- To: davidb158@xxxxxxxxxxx
- Date: Fri, 26 Oct 2007 17:14:54 -0400
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@xxxxxxxxxxx
> To: oracle-l@xxxxxxxxxxxxx
> 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>
>
Other related posts:
