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
Ensuring Backward Compatibility   The PL/SQL Version 2 permits some abnormal behavior which Version 8 disallows. Particularly, Version 2 permits you to (i) Make the forw

How Bulk Binds Improve Performance The assigning of values to the PL/SQL variables in SQL statements is known as binding. The binding of the whole collection at once is know

How Calls Are Resolved? The figure shows that how the PL/SQL compiler resolves the subprogram calls. When the compiler encounters the procedure or function call, it tries to di

Some Varray Examples In SQL Plus, assume that you define an object type Project, as described below: SQL> CREATE TYPE Project AS OBJECT ( 2 project_no NUMBER(2), 3 title VARCHA

Parameter and Keyword Description: type_name: This identifies a user-defined type specifier that is used in the subsequent declarations of the objects. AUTHID Clause:

UNION and OR - SQL SQL supports UNION explicitly but differently from the way it supports JOIN explicitly. As we have seen, JOIN is used exclusively within the FROM clause, su

%ISOPEN The %ISOPEN yields TRUE if its cursor or cursor variable is open; or else, the %ISOPEN yields FALSE. In the illustration, you use the %ISOPEN to select an action:

Definition of CROSS JOIN - SQL Let s = t1 CROSS JOIN t2, where t1 and t2 are table expressions optionally accompanied by range variables. Then: Note: Here T denotes Table

Example of DELETE - SQL As with UPDATE, a FOR PORTION OF clause can be specified if the target table has a defined period name, as illustrated in Example. Example: Deleting

Package Body: The package specification is implemented by the package body. That is, the package body has the definition of every cursor and the subprogram declared in the pac