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
Question: (a) In the context of database security explain how the following database features help to enforce security in the database system: (i) Authorisation (ii) Access

Example of UNWRAP Operator - SQL Example here shows how unwrapping can be done in longhand in SQL. Example: Unwrapping in SQL Letting CONTACT_INFO_WRAPPED denote the res

PITS Depressions in secondary cell wall is called pit. A pit present on the free cell wall surface without its partner is called Blind pit. It consists of 2 parts -

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:

Deleting Objects You can use the DELETE statement to eradicate objects from an object table. To eradicate objects selectively, you use the WHERE clause, as shown below: BEG

Read-Only Operator (+) - SQL The term read-only operator to the mathematical term function. Here I just need to add that the SQL standard reserves the term function for read-

Effects of NULL for UNIQUE Specification When a UNIQUE specification u for base table t includes a column c that is not subject to a NOT NULL constraint, the appearance of sev

EXCEPTION_INIT Pragma The pragma EXCEPTION_INIT relates an exception name with an Oracle error number. Which allow you to refer to any internal exception by the name and to wri

Using Pragma RESTRICT_REFERENCES: The function called from the SQL statements should obey certain rules meant to control the side effects. To check for violation of the rules,

Functions   The function is a subprogram that calculates a value. The Functions and procedures are structured similar, except that the functions have a RETURN clause. You can