Re: PL/SQL index-by table - how does it work?
- From: Tim Hall <tim@xxxxxxxxxxxxxxx>
- To: sbecker6925@xxxxxxxxx
- Date: Thu, 7 Apr 2011 08:16:00 +0100
Hi.
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.
http://www.oracle-base.com/articles/9i/BulkBindsAndRecordProcessing9i.php#bulk_collect
Like I said, sorry if I've missed the point of your question. :)
Cheers
Tim...
On Wed, Apr 6, 2011 at 9:25 PM, Sandra Becker <sbecker6925@xxxxxxxxx> wrote:
> Version EE 10.2.0.4
>
> 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.
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
