SQL/XML functions slow down queries?
Date: Wed, 03 Sep 2008 16:29:49 +0200
Does someone could explain why the SQL/XML functions slow down queryies so dramatically?
This piece of code takes a few seconds to complete:
type recType is record (
type arrType is table of recType ;
arr arrType ;
select col1,col2 bulk collect into arr from bigtable order by col1,col2; dbms_output.put_line('count=>'||arr.count) ; end ;
While this seem to take forever:
type xmlArrayType is table of xmlType ; xmlArr xmlArrayType ;
select xmlElement("ROW",xmlForest(col1,col2)) bulk collect into xmlArr from bigtable order by col1,col2;
dbms_output.put_line('count=>'||xmlArr.count) ; end ;
I'm using Oracle 10.2.0.3 on Linux. Bigtable has 2072904 rows and the optimizer uses the first two columns of the primary key to sort on col1 and col2 on both cases.
Thank you. Kind regards, Yossarian. Received on Wed Sep 03 2008 - 09:29:49 CDT