Nested tables versus index-by tables, PL-SQL Programming

Nested Tables versus Index-by Tables

The Index-by tables and nested tables are just similar. For e.g.  They have similar structure and their individual elements are accessed in the similar way (by using subscript notation). The main distinction is that the nested tables can be stored in a database column (and hence the word "nested table") while the index-by tables cannot.

The Nested tables extend the functionality of the index-by tables by letting you SELECT,
INSERT, DELETE, and UPDATE nested tables stored in the database. (Keep in mind, that index-by tables cannot be stored in the database). Some collection methods also operate only on the nested tables and varrays. For example, the built-in procedure TRIM cannot be applied to the index-by tables.

Another merit of the nested tables is that an uninitialized nested table is automatically null (that is, the table itself is null, not its elements), while an uninitialized index-by table is simply empty. Therefore, you can apply the IS NULL comparison operator to the nested tables but not to index-by tables.

Though, index-by tables also have some merits. For example, the PL/SQL supports implicit (automatic) datatype conversion between the host arrays and index-by tables (but not nested tables). Therefore, the most efficient way to pass collections to and from the database server is to use the anonymous PL/SQL blocks to bulk-bind input and output host arrays to the index-by tables.

Posted Date: 10/3/2012 6:29:32 AM | Location : United States







Related Discussions:- Nested tables versus index-by tables, Assignment Help, Ask Question on Nested tables versus index-by tables, Get Answer, Expert's Help, Nested tables versus index-by tables Discussions

Write discussion on Nested tables versus index-by tables
Your posts are moderated
Related Questions
Using FORALL and BULK COLLECT Together You can unite the BULK COLLECT clause with the FORALL statement, in that case, the SQL engine bulk-binds column values incrementally. In


UNION ALL - SQL Further varieties of UNION arise when we replace the key word DISTINCT by ALL in any of the foregoing examples, as in Example. ALL specifies that if row r appe

Literature review

Parameter and Keyword Description: EXIT: An unconditional EXIT statement (i.e., one without a WHEN clause) exits the present loop instantly. The Execution resumes with th

Overview of control structures According to the structure theorem, any computer program can be written by using the basic control structures as shown in figure below. They can b

Using TRIM This process has two forms. The TRIM removes an element from the end of the collection. The TRIM(n) removes the n elements from the end of the collection. For e.g.

Scope Rules You cannot declare an exception twice in the similar block. Though, you can, declare the similar exception in 2 different blocks. The Exceptions declared in a bloc

Biconditional - SQL The biconditional p ↔ q can be expressed in Tutorial D by p = q and the same is true of SQL. The question then arises as to whether, in SQL, p = q is equiv

Consider the schema for FreeChecking Bank, that we designed given below. Translate the given ER schema into SQL CREATE TABLE statements (indicating primary key, unique and foreign