Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sort + external procedure tuning

Re: sort + external procedure tuning

From: Stephan Bressler <stephan.bressler_at_siemens.com>
Date: Thu, 12 Dec 2002 12:53:30 +0100
Message-ID: <at9t9u$sua$1@news.mch.sbs.de>

> Hello,
> I test a query on db2 udb v7.2 EE and Oracle8i EE. On db2 it took me
> less than 30s. On Orale server it took me 10m.
> My query is like:
> SELECT id1, function_name(col2, 'Str1', col3, 'Str2')
> FROM MyTable
> Order by 2 desc;
>
> We use C/C++ external procedure. My table size is about 500M, 1M rows.

  1. function_name is the external procedure? --> need to be called for every row, thus 100M times
  2. Table with 1M rows, no where-clause but with "order by" --> Oracle need to get _all_ rows, sort them "by 2" and return after that the first rows to client

Seems that 10 Minutes is quite fair for such a query. I'm pretty sure, there's no parameter tuning to solve this.

TUNE YOUR SQL!!! Stephan Received on Thu Dec 12 2002 - 05:53:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US