Storing XML Documents in XMLTYPE or VARCHAR Column Type

You can store XML in your table using XMLTYPE column data type or the good old VARCHAR2 provided that XML document is no more than 4000 characters.

I made a quick comparison between both options to help you access which option is a better choice for your situation.

Test environment: Oracle 11.2.0.3, 64-bit running on Red Hat Linux 64-bit.

Test case setup: Created 2 tables, each table has a single column only. One table with column type VARCHAR2 (4000) while the other table XMLTYPE column type.

Test Case Result Comment
Space utilization Insert 65,000 rows XML rows in both tables VARCHAR table Size: 520 MB.

XML table Size: 168 MB. Note that are an additional LOB segment and a LOB index segments created for XML type.

Storing XML data in column type XMLTYPE saved 3X space as compared to VARCHAR table. That is expected because XMLTYPE is stored in binary format.
Syntax Checking Insert data with incorrect XML format XMLTYPE table rejected the row, while VARCHAR table accepted data This might be viewed as positive or negative point depending on your situation.
Insert Performance Insert 4,000 rows sequentially.  A single commit at the end. VARCHAR table: 0.3 sec

XMLTYPE table: 1.3 sec

VARCHAR table 3X faster than XMLTYPE.

None of the tables had indexes.

Querying table  based on an value within a XML document Create index on xml table with “extractvalue” option.

No index created on VARCHAR table

XML is always faster that VARCHAR table in retrieving data There is so much flexibility and performance gains in retrieving data with XMLTYPE column type than VARCHAR table

Hope this helps

Hazem

Advertisements