Re: What is the fastest/easiest way to find a row in a PL/SQL table

Hi, Rod

If the values are random and the indices are sequential, then you might have to loop. The question is:

Is the table large enough to make it worth to improve performance in regards to your programming effort.

If the sequential search really constitutes a performance problem, then maybe you can populate the table already ordered alphabetically (allowing you to perform a binary search), or
you might want to create the indices as hashes of the string value (allowing you direct access to the values for comparision).

Another solution might be availabe from http://asktom.oracle.net where you might want to search for 'cast binary_integer' for some type of 'select from PL/SQL table' mechanism (I have no idea how that works out performance-wise).

HTH Dieter

• Original Message ----- From: "Rod J. Stewart" <rstewart_at_eucalypt.net.au> Newsgroups: comp.databases.oracle.server Sent: Tuesday, February 12, 2002 10:16 AM Subject: What is the fastest/easiest way to find a row in a PL/SQL table

> Hi,
>
> DB version: 8.1.6: I have a package that populates a simple PL/SQL
> table declared like:
>
> TYPE MyType IS TABLE OF varchar2(10) INDEX BY BINARY_INTEGER;
>
> MyTable MyType;
>
> The table is populated via a cursor. My question is, what is the
> fastest performing way of locating a row in the table based on a
> supplied varchar2 value? Do I have to loop through each row of the
> table performing a match as in the following:
>
> for i in 1..MyTable.Count loop
> vValue := MyTable(i);
> if vValue = pValue then
> vResult := 'Y';
> end if;
> end loop;
>
> or is there a quicker way?
>
> Thanks in advance!
>
> Rod

"Rod J. Stewart" <rstewart_at_eucalypt.net.au> schrieb im Newsbeitrag news:dd07ce52.0202120116.463b79d9_at_posting.google.com...
> Hi,
>
> DB version: 8.1.6: I have a package that populates a simple PL/SQL
> table declared like:
>
> TYPE MyType IS TABLE OF varchar2(10) INDEX BY BINARY_INTEGER;
>
> MyTable MyType;
>
> The table is populated via a cursor. My question is, what is the
> fastest performing way of locating a row in the table based on a
> supplied varchar2 value? Do I have to loop through each row of the
> table performing a match as in the following:
>
> for i in 1..MyTable.Count loop
> vValue := MyTable(i);
> if vValue = pValue then
> vResult := 'Y';
> end if;
> end loop;
>
> or is there a quicker way?
>
> Thanks in advance!
>
> Rod
Received on Tue Feb 12 2002 - 04:42:57 CST