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
Use of Table Expressions - Expressing Constraint Conditions With the exception of key constraints, the examples in the theory book all explicitly reference at least one relvar

Database Values You can use the SELECT statement to have the Oracle assign values to a variable. For Each and every item in the select list, there must be a matching, type-compa

Advantages of Packages The benefits of the Packages are as shown below: Modularity The Packages encapsulate logically associated items, types, and subprograms in the

Using Subqueries A subquery is a query (typically enclosed by parentheses) that appears within another SQL data manipulation statement. If evaluated, the subquery gives a va

Parameter & Keyword Description: function_name: The user-defined function is identifying by that keyword. parameter_name: This identifies the formal parameter that

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,

Using the student and faculty tables create a select query that outputs all students for a specific advisor. Generate the execution plan, select out the explain plan . Create an

Adding Table Constraints ALTER TABLE ENROLMENT ADD CONSTRAINT NameNotNull CHECK (Name IS NOT NULL) ; ALTER TABLE ENROLMENT ADD CONSTRAINT PK_StudentId_CourseId PRIM

"Not Enforced" Table Constraints A constraint that is not enforced is not really a constraint within the meaning of the act, but SQL does have such a concept and it needs to b

Need for Dynamic SQL: You need dynamic SQL in the situations as follows: 1) You would like to execute a SQL data definition statement (like CREATE), a data control statemen