Varrays versus nested tables, PL-SQL Programming

Varrays versus Nested Tables

The Nested tables are differing from varrays in the following ways:

1)  Varrays have a maximum size, while nested tables do not.

2)  Varrays are always dense, while nested tables can be sparse. Therefore, you can delete individual elements from a nested table but not from a varray.

3) The Oracle stores varray data in-line (in similar tablespace). But, the Oracle stores nested table data out-of-line in a store table, that is a system-generated database table related with the nested table.

4) When stored in the database, the varrays retain their ordering and subscripts, while nested tables do not.

Which collection type must you use? That totally depends on your wants and the size of the collection. The varray is stored as an opaque object, while a nested table is stored in a storage table with each element mapped to a row in the table. Therefore, if you want efficient queries, then use the nested tables. If you want to retrieve the whole collections as a whole, then use varrays. Though, when collections get very large, it becomes impractical to retrieve more than subsets. Therefore, varrays are better suited for the small collections

Posted Date: 10/3/2012 6:31:47 AM | Location : United States

Related Discussions:- Varrays versus nested tables, Assignment Help, Ask Question on Varrays versus nested tables, Get Answer, Expert's Help, Varrays versus nested tables Discussions

Write discussion on Varrays versus nested tables
Your posts are moderated
Related Questions
Substitution and Instantiation - SQL It shows how NULL might appear in substitution for a parameter of a predicate and how it might thus participate in instantiation of that p

Enrolment was split - SQL Example shows how relvars IS_CALLED and IS_ENROLLED_ON can be derived from the original ENROLMENT relvar, using projection in the initial assignment

Use the RETURNING Clause Frequently, the application requires information about the row affected by a SQL operation, for illustration, to produce a report or take a subsequent

Sequential Control Dissimilar to the IF and LOOP statements, the GOTO and NULL statements are not important to the PL/SQL programming. The configuration of PL/SQL is such that th

Negation (NOT, ¬) - SQL There are three rows instead of just two. As you can see, ¬ p is defined as in two-valued logic (2VL) when p is either true or false, but ¬ (unknown) i

%ROWTYPE: This attribute gives a record type which represents a row in the database table or a row fetched from a formerly declared cursor. The Fields in the record and corresp

Transactions in SQL BEGIN TRANSACTION, COMMIT, and ROLLBACK, SQL has the same syntax except for START in place of BEGIN. However, START TRANSACTION is used only for outermost

Procedures   The procedure is a subprogram which performs a specific action. You write procedures using the syntax as shown below: PROCEDURE name [(parameter[, parameter, .

Committing and Rolling Back The COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. As the figure shows, if one transaction ends,

The 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 p