Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!border1.nntp.dca.giganews.com!nntp.giganews.com!newsfeed.iinet.net.au!newsfeed.iinet.net.au!per-qv1-newsstorage1.iinet.net.au!per-qv1-newsstorage1.iinet.net.au!per-qv1-newsreader-01.iinet.net.au!not-for-mail
Message-ID: <42A4108C.7967@yahoo.com>
Date: Mon, 06 Jun 2005 16:59:56 +0800
From: Connor McDonald <connor_mcdonald@yahoo.com>
Reply-To: connor_mcdonald@yahoo.com
X-Mailer: Mozilla 3.01 (WinNT; I)
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.server
Subject: Re: High Performance Server Side Programming - Which Language
References: <1117739186.987210.146160@g49g2000cwa.googlegroups.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 47
NNTP-Posting-Host: 203.59.172.152
X-Trace: 1118048394 per-qv1-newsreader-01.iinet.net.au 10183 203.59.172.152
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:244305

nicholas.wakefield@gmail.com wrote:
> 
> Hi,
> 
> I have a static list of 100,000 values in an array, given a string I
> generate a hashcode and return an item from this array. I could join to
> another table but everything has to be done in a function.
> 
> e.g select myFunc(field1) from myTable;
> 
> Any ideas what Oracle approach (language) would be best for this? So
> far I've tried done the following.
> 
> PL/SQL Table or Varray instantiated within a package (Natively compiled
> and interpreted)
> 4 seconds to translate 160k records.
> 
> Java static constants across multiple classes (constant pool size limit
> would be reached with a single class)
> 33 seconds to translate 160k records
> 
> and calling lower() instead of my function takes 1 second to pass 160k
> records, if i put lower in my package it also takes 3-4 seconds.
> 
> So any ideas, e.g. if I write the function in C and link it, would that
> work well.
> 
> Thanks in advance.

9.2 and above, your strings *can* be the index into a plsql table if
that helps.

hth
connor
-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------