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
Perform the following queries on the Hospital1.DB using SQL Anywhere (START EARLY!). a. Which patients have purchased the drug "Tylenol"? List the names and addresses. Arrange the

Rollback Behavior When a FORALL statement fails, the database changes are rolled back to an implicit savepoint marked before each of the SQL statement execution. The Changes t

Parameter SELF in pl/sql The MEMBER methods recognize a built-in parameter named SELF that is an instance of the object type. Whether declared explicitly or implicitly, it is

Scope and Visibility The References to an identifier are resolved according to its visibility and scope. The scope of an identifier is that area of a program unit (subprogram, b

First Step at defining type SID in SQL CREATE TYPE SID AS ( C VARCHAR(5) ) ; Explanation: TYPE SID announces that a type named SID is being defined to the syst

Equivalences & Rewrite Rules: If notice that as well as allowing us to prove trivial theorems, and tautologies enable us to establish that certain sentences are saying the sam

How Exceptions Are Raised By the run-time system, the internal exceptions are raised implicitly as are user-defined exceptions that you have related with an Oracle error number

Using DEFAULT You can use the keyword DEFAULT rather than that of the assignment operator to initialize the variables. For e.g. the declaration blood_type CHAR := ’O’; it can b

Comparison Operators The Comparison operators can compare one expression to another. The outcome is always true, false, or null. Usually, you use a comparison operators in condi

Magento change address format depending on store Project Description: What I need is that depending on the store in which the customer bought the address should change the fo