SQL/XML functions slow down queries?

From: yossarian <yossarian99_at_operamail.com>
Date: Wed, 03 Sep 2008 16:29:49 +0200
Message-ID: <48be9f5d$0$11378$5fc30a8@news.tiscali.it>

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 (
    col1 varchar2(80),
    col2 date
  ) ;
  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 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

Original text of this message