Re: PL/SQL index-by table - how does it work?

From: Tim Hall <>
Date: Thu, 7 Apr 2011 08:16:00 +0100
Message-ID: <>


It's difficult to know what you are trying to achieve from the information you have given, but I'll wing an answer anyway. It's possible I've totally missed the point of your question. If so, sorry in advance. :)

An index-by-table (also known as Associative Array) is a type of collection. Like all collections (Associative Array, Nested Table, VARRAY), it's just an array in memory. One benefit of an associative array is it has an index (BINARY_INTEGER or VARCHAR2) that allows you to reference an individual row, rather than search the collection for it. As a result, if you set the index value of a row to match the primary key, you can quickly access the row from the collection. If you need this type of row interaction, then the index-by-table is better than the nested table. If you are just going to walk through all the rows from first to last, then you are at no advantage.

As far as your case is concerned, the code suggests you are just pulling data back from a cursor FOR LOOP to populate the collection (via an object constructor call). You could certainly do this more efficiently by bulk collecting into a nested table if the constructor was in the query, like maybe:

SELECT InvoiceLineItem_obj(col1, col2, col3...) BULK COLLECT INTO li_nt_array
FROM ... -- rest of query making up the "li" cursor reference.

Remember, collections are just arrays in memory, so you may end up hogging all the system memory if this query returns many rows. Here is a quick overview of bulk binds in PL/SQL.

Like I said, sorry if I've missed the point of your question. :)



On Wed, Apr 6, 2011 at 9:25 PM, Sandra Becker <> wrote:
> Version EE
> Has anyone used the PL/SQL index-by table?  I have a developer who wants to
> know to if it is better than using nested tables for updates on about a
> dozen related tables.  I've never had any experience with it and don't know
> how it works, what would be better, etc.  I read through a post on AskTom
> that says it would be better, but the post is 9 years old.  Is this still a
> viable alternative to nesting a dozen tables?
> The example code that the developer gave me:
> FOR li_rec IN li LOOP
>           li_array.EXTEND;
>           li_array(li_array.LAST) :=
>                  InvoiceLineItem_obj(
> This is all he provided.  He says our code is just creating oracle objects
> and passing them back to java where they are instntiated.  That's all the
> information I have from the developer other than "it's taking too long" but
> no actual information on how long is too long.
> --
> Sandy
> Transzap, Inc.

Received on Thu Apr 07 2011 - 02:16:00 CDT

Original text of this message