Index by table methods

A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection
With Oracle 7, you could create what was called an index-by table, or 'PL/SQL Table', that consisted of a series of value pairs; an index value, and a scalar datatype (such as varchar2, or number). You referred to an individual PL/SQL Table entry by using the index, i.e. CUSTOMER_NAME (10). What made it interesting was that, as well as using scalar datatypes, you could also create PL/SQL tables using PL/SQL records, which could consist of a number of individual columns. By creating a PL/SQL record type based off of an existing table (for example, by using the SCOTT.EMP%ROWTYPE), you could load a table row, or an entire table, into a variable and process it within your PL/SQL package. As of Oracle 8, PL/SQL Tables were renamed 'Collections' and supplemented by two new composite types: Nested Tables, and VARRAYs. Nested tables extend the functionality of index-by tables by adding extra collection methods (known as table attributes for index-by tables), and, in a new development, nested tables can also be store in database tables and can be directly manipulated using SQL. Collectively, both types are known as PL/SQL Tables. There are some key differences between traditional index-by tables, and nested tables one major difference between nested tables and index-by tables, is that you have to initialize nested tables, using a constructor (like you get in java), defining how many elements can initially be stored in it (although you can later EXTEND the nested table); however, as mentioned above, you can store nested tables within the database (embedded in database columns), which is a distinct advantage over index-by tables. So, if you want to put together a database that is object orientated, and you need the data to be persistent, nested tables are the way to go.