Re: SQL/XML functions slow down queries?

xmltype is a clob, so it is very expensive (direct writes) to create a large table of xmltype.

If you can live with a single xml document, then this will be less expensive.

declare

   l_xml xmlType;
   x number;
begin
  select count(*),
xmlelement("XML",xmlagg(xmlElement("ROW",xmlForest(col1,col2))))   into x, l_xml
  from bigtable order by col1,col2;

  dbms_output.put_line('count=>'|| x) ;

end ;

count=>1704515

PL/SQL procedure successfully completed. Elapsed: 00:01:15.66 Received on Wed Sep 03 2008 - 13:51:30 CDT