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: ** table with clob slow

Re: ** table with clob slow

From: Mad Amruthur <madorcl_at_gmail.com>
Date: Thu, 29 Jun 2006 12:37:42 -0700
Message-ID: <933675cd0606291237t4c460e9k1c5bca9809ef9902@mail.gmail.com>


Hello,
I had the same issue about a couple of months ago. We have 1 table that's 80GB with a CLOB column and its all documents and images. I could not use TTS as the character set was changing from single to a double byte character set and exp imp was the only option I had.

The export took about 5 hours and import about 34 hours !!!

Since this was not going to work, I had an id column in the table and based on the distribution I generated multiple export files with almost equal ID ranges using the query clause. I also did the import in parallel ( 4 files at a time, had 10 files). The export was done in about 90 mins and the import took about 6 hours....

So if you can break the export into multiple export files using the query parameter, you can run the imports in parallel on the target and that should be faster

Thanks
Madhavan

On 6/23/06, Leng Kaing <Leng.Kaing_at_hsntech.com> wrote:
>
> Hi,
>
>
>
> If you think exporting CLOBs are slow, wait till you import! It's much
> much slower.
>
>
>
> During imports, depending on the size of your clob column, you may be able
> to get away with COMMIT=N. I've found this to be much faster than COMMIT=Y.
> However, it means that you must have a LARGE undo retention and tablespace,
> otherwise your import will fail.
>
>
>
> The other trick we've found is that even though we have clob columns
> defined in the tables, they are actually empty. So doing a "sql unload" and
> sqlload without the clob columns will cut down your times.
>
>
>
> HTH,
>
>
>
> Leng.
>
> -------------------------
>
>
>
> Date: Thu, 22 Jun 2006 19:35:23 -0700 (PDT)
>
> From: A Joshi <ajoshi977_at_yahoo.com>
>
> Subject: ** table with clob slow
>
>
>
> Hi,
>
> We have a big table and it has one field with CLOB datatype. Doing an
> export on this table is very slow. I have tried with different buffer sizes
> of 2MB - 250MB. It is still slow. Any way to speed up export and other
> operations on the table like update,delete. Thanks for your help.
>
>
>
>
>
> ------------------------------------------------------------------
>
> Leng Kaing
>
> Senior Oracle DBA
>
> Hansen Technologies; 2 Frederick St; Doncaster 3108
>
> Ph: +61-3-9840-3832
>
>
> Notice:
>
> This e-mail and any attachments are confidential and may contain legally
> privileged information and/or copyright material of Hansen Technologies
> Limited or third parties. Copying, distributing, disclosing, commercialising
> or otherwise acting in reliance on this e-mail and any attachments is
> strictly prohibited unless you are the addressee of this e-mail and have
> written permission to do so. If you have received this e-mail in error
> please delete this e-mail (including any copies and attachments) and contact
> Hansen Technologies Limited by return e-mail or by telephone on + 61 3 9840
> 3000. Any views expressed in this e-mail are those of the individual sender
> and may not necessarily reflect the views of or be a commitment by the
> organisation, except where the individual sender has the authority and
> expressly states them to be so.
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jun 29 2006 - 14:37:42 CDT

Original text of this message

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