1) trick question, the only answer is:

neither, you do not process huge volumes of data in memory, in collections/index by tables (plsql index by tables are all associated arrays, collections are the other kind of array).

You process a bit of data at a time, not tens of thousands, not millions of entries at a time.

So, when doing it right - a piece at a time, a few hundred/thousand (low thousands) - you use which ever one is easier.

I myself prefer index by tables, they work much more like an array - you don't have to .extend them. They are nice also in that they can be sparse if it makes sense - and it gives you a really easy way to look up something - either by a number or a string.


2) index by tables are sparse - that much is documented. That internally they are a b*tree isn't so relevant to us. That they are sparse - is. What if you had to store index elements -2000000000 and +2000000000 for whatever reason.

ops$tkyte%ORA10GR2> declare
  2          type array is table of date index by binary_integer;
  3          l_data array;
  4  begin
  5          l_data(-2000000000) := sysdate;
  6          l_data(+2000000000) := sysdate+1;
  7  end;
  8  /

PL/SQL procedure successfully completed.

that is nice, it doesn't allocate all 4,000,000,000 elements - just the two it needs.

If you use a collection for that - think about what you would have to .extend to :)

collections in plsql variables are not sparse.

Whether that impacts you or not - is up to your design, your implementation.

Most of the times, I use plsql index by tables - as arrays.

Collections are nice if you want to reference the collection in SQL directly - if that is necessary, the collections would be the way to go.


So, for much of my code - plsql index by tables - no .extend, sparse - but no direct interaction with SQL, no easy way to initialize them..

Collections - not sparse, have to allocate them, direct interaction with SQL - and a nice initialization method.