Here I am going to explain you about what are the major differences between VArrays and Nested Tables.
- Nested tables are unbounded.
- Initially dense but can become sparse through deletions.
- Order is not preserved
- Can be indexed
- VARRAYs are always bounded (varying arrays have a limited number of entries)
- Never sparse.
- When you store and retrieve a VARRAY, its element order is preserved.
- Cannot be indexed
According to O Reilly Book the main difference between Index-By Table (pl-Sql Table) Varray and nested tables are –
|Column1||Index-By Table||Nested Table||VARRAY|
|Usable in SQL?||No||Yes||Yes|
|Usable as column datatype in a table?||No||Yes; data stored out of line (in separate table)||Yes; data stored in line (in same table)|
|Uninitialized state||Empty (cannot be null); elements undefined||Atomically null; illegal to reference elements||Atomically null; illegal to reference elements|
|Initialization||Automatic when declared||Via constructor fetch assignment||Via constructor fetch assignment|
|In PL/SQL elements referenced via||BINARY_INTEGER||Positive integer between 1 and 2 147 483 647||Positive integer between 1 and 2 147 483 647|
|(-2 147 483 647 .. 2 147 483 647)|
|Sparse?||Yes||Initially no; after deletions yes||No|
|Bounded?||No||Can be extended||Yes|
|Can assign value to any element at any time?||Yes||No; may need to EXTEND first||No; may need to EXTEND first and cannot EXTEND past upper bound|
|Means of extending||Assign value to element with a new subscript||Use built-in EXTEND procedure (or TRIM to condense) with no predefined maximum||EXTEND (or TRIM) but only up to declared maximum size|
|Can be compared for equality?||No||No||No|
|Retains ordering and subscripts when stored in and retrieved from database?||N/A||No||Yes|