Fast re-build of tables with LONG RAW columns?

[Sarah23]

Hi,

I work in a SAP environment where the backend database is Oracle 9i. I use SAP's SAPDBA tool when reorganizing/re-building tables and indexes. The SAPDBA tool uses Oracle's Export & Import command, along with Drop & Create SQL scripts to do the re-builds. Everyting is taken care for you by the SAPDBA tool ie recreating any constraints etc. However the Export & Import approach is very very slow when re-building tables with a LONG or LONG RAW column... takes hours for some tables!!!

Can someone please tell me if there is a fast way of re-building tables with LONG or LONG RAW columns?

Thanks

Sarah J

 

[SantaMufasa]

Sarah,

As a youngster, did your school teacher ever require your class to read John Steinbeck's "Of Mice and Men"? In that book, one of the central characters was "Lenny", a very big, but very low-intelligence fellow. Lenny had good intentions and a big heart, but due to his limitations, was not as useful/functional as he could have been.

Well, Oracle's LONG and LONG RAW data types are "Lennys": big and well intentioned, but unfortunately very low intelligence and limited in their functionality.

You can reorganise tables in lightening speeds if the table does not contain LONG* columns, by using this syntax:

ALTER TABLE <tablename> MOVE PARALLEL NOLOGGING;

Reorganizing with this method is manifold faster than export/import (e.g. ~6MB/sec on average on my servers). If you use this method, you must also re-organise the tables indexes. For that I use:

alter index <indexname> rebuild parallel;

But, alas, for tables containing LONG* columns, Oracle's only reorganisation method (as far as I am aware) is export/import.

Also, are you aware that Oracle discourages the use of LONG* columns in favour of CLOB/BLOB columns? CLOB columns have the 2GB maximum size, but you can treat them as you would a VARCHAR2 column.

Let us know if this answers your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Sarah23]

Hi Dave,

Thanks for the quick reply... even if it wasn't the answer I was looking for!

As for using CLOB/BLOB over LONG, not much I can do about that seeing as the database is part of a SAP ERP system.

Thanks again.

Sarah J

PS: I liked your analogy with the character lenny from the book "Of Mice & Men"... made me smile.