What is difference between Index By Tables,Nested Tables and Varrays?
The chart below lists the properties of the Index By Tables,Nested Tables and Varrays
| Index By Table | Nested Table | Varrays |
|---|---|---|
| Subscript Characteristics : Can be arbitrary numbers or strings. Need not be sequential. | Subscript Characteristics : Sequential numbers, starting from one. | Subscript Characteristics : Sequential numbers, starting from one. |
| Size : Unbounded i.e. the number of elements it can hold is not pre-defined | Size: Unbounded i.e. the number of elements it can hold is not pre-defined | Size: Bounded i.e. holds a declared number of elements, though this number can be changed at run time |
| Database Storage: Index by tables can be used in PL/SQL programs only, cannot be stored in the database. | Database Storage: Can be stored in the database using equivalent SQL types, and manipulated through SQL. | Database Storage: Can be stored in the database using equivalent SQL types, and manipulated through SQL (but with less ease than nested tables) |
| Referencing and lookups: Works as key-value pairs.e.g. Salaries of employees can be stored with unique employee numbers used as subscripts sal(102) := 2000; | Referencing and lookups: Similar to one-column database tables. Oracle stores the nested table data in no particular order. But when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. | Referencing and lookups: Standard subscripting syntax e.g. color(3) is the 3rd color in varray color |
| Flexibility to changes: Most flexible. Size can increase/ decrease dynamically. Elements can be added to any position in the list and deleted from any position. | Flexibility to changes: Almost like index-by tables, except that subscript values are not as flexible. Deletions are possible from non-contiguous positions. | Flexibility to changes: Not very flexible. You must retrieve and update all the elements of the varray at the same time. |
