We have a large table, a column stores xml data as binary xmltype storage, and XMLTABLE query is used to extract the data.
If we just need to extract data into a column, and the data has no relation with other data columns, XMLTABLE query is super fast.
Once the data has parent -> children relationship with other columns, the query becomes extremely slow. From the query plan, we could observe that the parallel execution is gone.
I can reproduce the problem with the following scripts:
1. Test scripts to setup
-- Test table
drop table test_xml;
CREATE table test_xml
( period date,
XMLTYPE COLUMN xml_content STORE AS SECUREFILE BINARY XML (
STORAGE ( INITIAL 64K )
enable storage in row
-- Populate test_xml table with some records for testing
insert into test_xml (period, xml_content)
select sysdate, xmltype('<?xml version = "1.0" encoding = "UTF-8"?>
</searchresult>') from dual;
for i in 1 .. 10
insert into test_xml select * from test_xml;
select count(*) from test_xml;
2. Fast query. Only extract room_id info, the plan shows parallel execution. The performance is very good.
explain plan for
room_id varchar2(4000) PATH './id/text()'