XML Type Vs Clobs

Before XMLType datatype was released in Oracle 9i Release 1, peoples were storing their data in Varchar2 or character large objects (CLOB).Because free-form text data was traditionally stored in VARCHAR2s or CLOBs, it seemed a logical fit for XML as well.

With the first release of Oracle9i Database, when you stored an instance of the XMLType datatype, the underlying storage model was a CLOB value. The entire document was read and stored as-is in a CLOB object in the database.


  CREATE TABLE "SCOTT"."T"
   (    "X" "SCOTT"."XMLTYPE"
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TOOLS"
 XMLTYPE COLUMN "X" STORE AS CLOB (
  TABLESPACE "TOOLS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) 



scott@10GR2> create table t_xmlclobs(x clob);

Table created.

scott@10GR2> create table t_xmltype (x xmltype);

Table created.

scott@10GR2> declare
  2     l_start_time number;
  3  begin
  4     l_start_time := dbms_utility.get_time;
  5     for i in 1..10000
  6     loop
  7             insert into t_xmlclobs(x) values ('');
  8     end loop;
  9     commit;
 10
 11     dbms_output.put_line(' Total Time for Clobs='|| ( dbms_utility.get_time - l_start_time) );
 12
 13     l_start_time := dbms_utility.get_time;
 14     for i in 1..10000
 15     loop
 16             insert into t_xmltype(x) values ('');
 17     end loop;
 18     commit;
 19
 20     dbms_output.put_line(' Total Time for Xmltype ='|| ( dbms_utility.get_time - l_start_time) );
 21  end;
 22  /
 Total Time for Clobs = 42
 Total Time for Xmltype = 1641

PL/SQL procedure successfully completed.
 
When an XMLType instance is created, an XML syntax check is performed—regardless of the storage architecture—to ensure that the XML document is well formed.