XMLType DOMDocument and pointers

Ran across this post on the OTN Forums last week.
Dynamically adding attribute based on PL/SQL function
The part that interested me was Odie's second post. If you look closely at his second post (first example) he basically does
=======================
xmltype := XML_text
DOMDocument := New Document (xmltype)
... modify DOMDocument
Free DomDocument
Output xmltype.getClobVal
 =======================
So with that example, he shows that even though the DOMDocument variable is being modified, the changes still show up in the XMLtype variable.  The only way that can be done is through the use of a pointer the two variables are using to reference the same memory.  Very interesting to me as it was something new.

XMLTYPE and DBMS_REDEFINITION

This post is based on the following OTN XML DB thread: Migrating XMLType from STORE AS CLOB to STORE AS BINARY XML.

When Oracle first introduced the XMLType datatype (9.2.0.3), the default storage mechanism for it was a CLOB (aka BasicFile LOB). Note: I'm ignoring Object Relational storage since it is a table based format instead of a single column. With the release of 11.1.0.6, Oracle added a new storage type of SECUREFILE BINARY XML but left the default storage as CLOB. With the release of 11.2.0.2, Oracle changed the default storage to be SECUREFILE BINARY XML (What's New in Oracle XML DB?).

So what are the options for going from CLOB to BINARY XML storage? One is via DBMS_REDEFINITION. This may be the best option as it allows for the redefinition of the table without any system downtime. The downside to this option is that you need to have free space equal to or greater than the size of the new table as both the old and new will exist at the same time in the system. Assuming you can, here is what a simple example looks like (borrowed from myself from the OTN thread)

SQL> CREATE TABLE HOLDS_XML  -- old table structure
  2    (n_col   NUMBER(5) NOT NULL PRIMARY KEY,
  3     xml_col XMLTYPE)
  4  XMLTYPE xml_col STORE AS BASICFILE CLOB;
 
Table created
SQL> INSERT INTO holds_xml VALUES
  2  ('1',XMLTYPE('val1'));
 
1 row inserted
SQL> COMMIT;
 
Commit complete
SQL> CREATE TABLE HOLDS_XML_TMP  -- new table structure
  2    (n_col   NUMBER(5) NOT NULL PRIMARY KEY,
  3     xml_col XMLTYPE)
  4  XMLTYPE xml_col STORE AS SECUREFILE BINARY XML;
 
Table created
SQL> exec DBMS_REDEFINITION.start_redef_table('JASON', 'HOLDS_XML', 'HOLDS_XML_TMP');
 
PL/SQL procedure successfully completed
 
SQL>    exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('JASON', 'HOLDS_XML', 'HOLDS_XML_TMP');
 
PL/SQL procedure successfully completed
SQL>    exec DBMS_REDEFINITION.finish_redef_table('JASON', 'HOLDS_XML', 'HOLDS_XML_TMP');
 
PL/SQL procedure successfully completed
 
SQL> SELECT dbms_metadata.get_ddl('TABLE','HOLDS_XML')
  2  FROM   dual;
 
DBMS_METADATA.GET_DDL('TABLE',
--------------------------------------------------------------------------------
snipped...
 XMLTYPE COLUMN "XML_COL" STORE AS SECUREFILE BINARY XML (
...snipped

As you can see from the above DDL, the column is now a SECUREFILE BINARY XML. This also means that all the XML in that column has been converted to the more efficient (and smaller storage requirements) BINARY XML.

While researching this post, I came across someone mentioning the use of an ALTER TABLE statement to switch the storage formats. Here is what happens on 11.1.0.6 if we use the above table with a row in it.

SQL> ALTER TABLE JASON.HOLDS_XML
  2     MODIFY (XML_COL)
  3       XMLTYPE COLUMN XML_COL STORE AS SECUREFILE BINARY XML;
 
Table altered
 
SQL> SELECT dbms_metadata.get_ddl('TABLE','HOLDS_XML')
  2    FROM   dual;

DBMS_METADATA.GET_DDL('TABLE',
--------------------------------------------------------------------------------
snipped...
 XMLTYPE COLUMN "XML_COL" STORE AS BASICFILE CLOB (
...snipped

So the command completed successfully but did nothing still the column still uses CLOB storage. It was worth a try at least.

There are also the standard methods of

• Exporting data, dropping table, creating new table, importing data
• Exporting data, creating new table, importing data, dropping old table, renaming new table
• Create new table, Insert Select From old table, dropping old table, renaming table

I'm sure there are plenty others, but all those method involve re-doing grants and privileges for the new table. Each has a place though, depending upon the situation and environment.

The downside to this is that once the table contains a SECUREFILE BINARY XML based XMLType column, you can no longer use DBMS_REDEFINITION as shown by

SQL> CREATE TABLE HOLDS_XML  -- new table structure
  2     (n_col   NUMBER(5) NOT NULL PRIMARY KEY,
  3      xml_col XMLTYPE)
  4   XMLTYPE xml_col STORE AS SECUREFILE BINARY XML;
 
Table created
 
SQL> exec dbms_redefinition.can_redef_table( 'JASON', 'HOLDS_XML' );
 
begin dbms_redefinition.can_redef_table( 'JASON', 'HOLDS_XML' ); end;
 
ORA-12090: cannot online redefine table "JASON"."HOLDS_XML"
ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1766
ORA-06512: at line 2

The ORA-12090 means

An attempt was made to online redefine a table that is either a clustered table, AQ table, temporary table, IOT overflow table or table with FGA/RLS enabled.

I searched on My Oracle Support but did not turn up any reason why a BINARY XML storage would cause this issue. Hopefully it is a limitation Oracle removes in the future.

I think this post xml query hungs up with large xml response from utl_http request on the OTN Forums and the response nicely sums up what I mentioned in my Options for Slow Performance Parsing Large XML series. If you want good performance for parsing large XML that is generated external to Oracle and you are on 11g then you need to INSERT the data into an XMLTYPE column stored as securefile binary xml and then query against that data.

XMLElement and namespaces

This post was inspired by the following thread in the OTN forum Formatting data to xml consumes more time. I did some Google searching and did not turn up any decent hits on the topic (or my searching was bad for the day) so I decided to do a quick write up on it.

The first place to start for learning how to generate XML data from an Oracle DB is of course the documentation. This can be found at Generating XML Data from the Database.

If you look through that document, there are not many references to namespaces and namespace prefixes. In fact, only Example 18-6 shows how to add a namespace to a node, but not how to assign a prefix to that node. I created the following example to show how to add a namespace and namespace prefix to nodes in XML.

SELECT XMLElement("t:global-instance",
                  XMLAttributes('your.namespace.uri.here' AS
                                  "xmlns:t"),
                  XMLElement("t:child1",'c1'),
                  XMLForest('c2' AS "t:child2",
                            'c3' AS "t:child3"),
                  XMLElement(evalname('t1:' || dummy), 'c4')).getClobVal() rslt 
  FROM dual;

which generates

<t:global-instance xmlns:t="http://your.namespace.here">
 <t:child1>c1</t:child1>
 <t:child2>c2</t:child2>
 <t:child3>c3</t:child3>
 <t1:X>c4</t1:X>
</t:global-instance>

That shows the basics for adding a prefix when using XMLElement and XMLForest. If you are using 10.2.0.3 or later, you can also use EvalName to generate the node name instead of hard-coding it as earlier versions required. In that case, I showed how to include the namespace prefix for EvalName as well. Yes it really is that simple. You just type in the prefix you want to use.

Options for Slow Performance Parsing Large XML - Part 3

The final piece to this chapter. In my previous post Options for Slow Performance Parsing Large XML - Part 2, I showed how registering a schema and having that process create a table could be used to increase the parsing speed of XML compared to parsing that XML via PL/SQL. Now I'm going to look at whether using a table without an associated schema will make any difference on the parsing/inserting speeds.

I again used the following block of code for running my tests

declare 
   l_result_xml   XMLTYPE;
   l_ns_xml       XMLTYPE;
   l_start        NUMBER;
   l_end          NUMBER;

begin
   -- Build up the XML
   -- Has 2100+ row nodes
   ...snipped...

   l_start := dbms_utility.get_time;
   INSERT INTO LOAD_TEMP_RESULT VALUES l_result_xml;
   l_end   := dbms_utility.get_time;
   dbms_output.put_line('INSERT INTO TEMP TIME in sec: ' || to_char((l_end - l_start) / 100));

   l_start := dbms_utility.get_time;
   INSERT INTO import_queue
   (...20 columns...) 
   SELECT CBIQ_ID_SEQ, CBRH_ID_SEQ,
          ...18 columns ...
     FROM LOAD_TEMP_RESULT ltr,
          XMLTABLE('/resultset/row'
                   PASSING ltr.object_value  -- column alias name
                   COLUMNS
                   CBIQ_ID_SEQ          NUMBER(11) PATH 'CBIQ_ID_SEQ', 
                   CBRH_ID_SEQ          NUMBER(11) PATH 'CBRH_ID_SEQ',
                   ... 18 more columns ...);
   l_end   := dbms_utility.get_time;
   dbms_output.put_line('INSERT INTO TIME in sec: ' || to_char((l_end - l_start) / 100));
   rollback;
end;

The only difference I changed was the CREATE TABLE statement for LOAD_TEMP_RESULT. The following table summarizes the various CREATE TABLE statements I used and the resulting times, where TEMP TIME is the output for "INSERT INTO TEMP TIME in sec:" and TIME is the output for "INSERT INTO TIME in sec:"

DDL	TEMP TIME (sec)	TIME (sec)
CREATE TABLE load_temp_result OF XMLTYPE;	0.05	16
CREATE TABLE load_temp_result OF XMLTYPE XMLTYPE STORE AS BASICFILE CLOB;	0.09	15.82
CREATE TABLE load_temp_result OF XMLTYPE XMLTYPE STORE AS SECUREFILE CLOB;	2.36	15.46
CREATE TABLE load_temp_result OF XMLTYPE XMLTYPE STORE AS BASICFILE BINARY XML;	0.27	0.63
CREATE TABLE load_temp_result OF XMLTYPE XMLTYPE STORE AS SECUREFILE BINARY XML;	0.24	0.63

As I am using 11.1.0.6, the default behavior for this version of Oracle is to store XMLTypes as a CLOB. This is confirmed by the fact that the first row (default storage) matches most closely with the second row (explicit storage). Based on the results, it is easy to see that even creating a XMLType table (or a XMLType column) where the XMLType is stored as BINARY XML is well worth looking at in terms of performance when validation is not needed.

I'm not going to go into a discussion on the differences between BASICFILE and SECUREFILE, but the highlights of what SECUREFILE offers can be found at LOB Features Introduced in Oracle Database 11g Release 1. Oracle recommends that SECUREFILE be used for as I have picked up from the XML DB forum on OTN.

To recap the results from the previous two threads on this topic,

• Using XMLTable in a SQL statement to parse a PL/SQL XMLType variable took 1032 seconds (16+ minutes).
• Using PL/SQL to loop through each node in the XML and parse the information out and then INSERT that into a table took 4 seconds.
• Using a registered schema and associated table took 0.5 seconds.

For the given test case I was using (approx 2100 row nodes in the XML) on the system I was using (11.1.0.6 on a RedHat OS), doing a schema registration that creates an associated table just edged out using a Binary XML XMLType based table at 0.87 seconds. This should be due to the additional information that Oracle knows about the XML based off the schema. As this was also the first version that BINARY XML existed in, it could be partially due to that fact as well. Maybe if I get access to a newer version of Oracle, I could test that theory.