Re: SQL/XML functions slow down queries?

On Sep 3, 9:29 am, yossarian <yossaria..._at_operamail.com> wrote:
> Does someone could explain why the SQL/XML functions slow down queryies
> so dramatically?
>
> This piece of code takes a few seconds to complete:
>
> declare
>   type recType is record (
>     col1 varchar2(80),
>     col2 date
>   ) ;
>   type arrType is table of recType ;
>   arr arrType ;
> begin
>   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:
>
> declare
>   type xmlArrayType is table of xmlType ;
>   xmlArr xmlArrayType ;
> begin
>   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.

You should take the basic queries:

select col1,col2 from bigtable order by col1,col2; select xmlElement("ROW",xmlForest(col1,col2)) from bigtable order by col1,col2;

and either run explain plan on each or enable autotrace, to see what Oracle is actually doing to return the results. You should get an idea of why the second query takes so much longer to complete.

David Fitzjarrell Received on Wed Sep 03 2008 - 10:35:10 CDT