Sparseness is what "Index By" table can offer

I received an email today (from oracle yahoo groups) asking about the reason on why index by clause is used in pl/sql tables and not for nested table?

Although I replied there but I thought it is good idea to share it with you all and I’ll appreciate your comments.

PL/SQL tab/index by tables use "index by BINARY_INTEGER clause" for their sparse capability.
When I say sparse capability, it means that you can assign a value to nth member of the array.

Ex:
“INDEX BY” table

SQL> declare
2 type char_array is table of varchar2(100) index by binary_integer;
3 name char_array;
4 begin
5 name(15) := 'Sachin';
6 end;
7 /
PL/SQL procedure successfully completed.


“Nested table”

SQL> declare
2 type char_array is table of varchar2(100);
3 name char_array :=char_array();
4 begin
5 name(15) := 'Sachin';
6 end;
7 /
declare
*ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 5

And see this :-

SQL> ed
Wrote file afiedt.buf
1 declare
2 type char_array is table of varchar2(100);
3 name char_array :=char_array();
4 begin
5 name.extend(15);
6 name(15) := 'Sachin';
7* end;SQL>
/
PL/SQL procedure successfully completed

So Nested table are not originally sparse, you have to extend them or delete the data to make them sparse.

In both nested table and “index by” table, the data is referenced by index value. In case of
“index by” table, it is BINARY_INTEGER (-2,147,483,647 to 2,147,483,647) and in case of “nested table”, it is integer value 1 to 2,147,483,647. But “index by” table offers initial sparseness which is not there in case of nested table.