This content has been marked as final. Show 3 replies
This (rough) approach should work for you.
How it differs from your approach.
CREATE TABLE HOLDS_XML (xml_col XMLTYPE) XMLTYPE xml_col STORE AS SECUREFILE BINARY XML; INSERT INTO HOLDS_XML VALUES (xmltype(bfilename('DIR_OBJ','large_819mb_file.xml'), nls_charset_id('UTF8'))) -- Should be using AL32UTF8 for DB character set with XML SELECT ... FROM HOLD_XML HX XMLTable(... PASSING HX.xml_col ...)
By using the HOLDS_XML table with SECUREFILE BINARY XML storage (which became the default in 22.214.171.124) we are providing a place for Oracle to store a parsed version of the XML. This allows the XML to be stored on disk instead of in memory. Oracle can then access the needed pieces of XML from disk by streaming them instead of holding the whole XML in memory and parsing it repeatedly to find the information needed. That is what COLLECTION ITERATOR PICKLER FETCH means. A lot of memory work. You can search on that term to learn more about it if needed.
The XMTable approach then simply reads this XML from disk and should be able to parse the XML with no issue. You have the option of adding indexes to the XML, but since you are simply reading it all one time and tossing it, there is no advantage to indexes (most likely)