CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Thomas Andreè Wang
Thomas Andreè Wang

Reputation: 3429

convert clob to xmltype, what is fastest xmltype() or xmlparse(document …)

Simple enough question, i have a large database and im about to convert a column of roughly 40GB (yes that column accounts for 40GB, in a table of 90) and 7-8 million rows so speed is of the essence.

The code im using is like this.

ALTER TABLE KJOERETOEY
RENAME column ORIGIN TO ORIGIN_OLD;
ALTER TABLE KJOERETOEY
  ADD ORIGIN XMLTYPE NULL;
UPDATE KJOERETOEY
SET ORIGIN = xmlparse(document ORIGIN_OLD);
ALTER TABLE KJOERETOEY
  drop column ORIGIN_OLD;

Then in my time idling and waiting for it to finish, i noticed that there exist a xmltype.createXML and the only downside i can see is that it will throw an error if the data is NULL, and that is ok as the data should not be null.

so the question.

Is 

UPDATE KJOERETOEY
SET ORIGIN = xmlparse(document ORIGIN_OLD);

faster or slower than

UPDATE KJOERETOEY
SET ORIGIN = xmltype.createXML(ORIGIN_OLD);

and are there any tricks i can make use of to increase speed of this type of operation.

Upvotes: 2

Views: 688

Answers (1)

wolφi
wolφi

Reputation: 8361

With 40 GB of XML data, I would assume that most of the time is spent writing the LOB data.

  1. I would not do it with an UPDATE but create a new table with the new structure.

  2. Furthermore, I'd put the old and the new column possibly in different tablespaces to make it easier to clean up afterwards.

  3. For XMLTYPE, I'd look into storing it as BINARY XML.

  4. I'd store it in SECUREFILE, not BASICFILE

  5. If you have the Advanced Compression option, I'd try to store the BINARY XML in a compressed form.

Test it with a smallish sample.

RENAME TABLE kjoeretoey TO kjoeretoey_old;

CREATE TABLE kjoeretoey (
  id number,
  origin XMLTYPE,
  ...
) XMLTYPE COLUMN origin STORE AS SECUREFILE BINARY XML (
  TABLESPACE my_xml_tablespace DISABLE STORAGE IN ROW COMPRESS HIGH 
);

INSERT INTO kjoeretoey (id, origin, ...) 
SELECT id, XMLTYPE(origin) ... FROM kjoeretoey_old;

DROP TABLE kjoeretoey_old;

Upvotes: 1

Related Questions