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
Control Structures The Control structures are the most important PL/SQL extension to the SQL. Not only does PL/SQL let you manipulate Oracle data, it lets you process the data

Effects of NULL in Table Literal When a VALUES expression appears as the source value for an SQL INSERT statement, the key word NULL can appear as a field value, such that for

Row Counterparts of Table Operators SQL does not have counterparts tuple rename, tuple projection, tuple extension, tuple join and tuple compose. To obtain the same effects as

Example of Shorthand for a row constraint Example: Shorthand for a row constraint ALTER TABLE EXAM_MARK ADD CONSTRAINT Mark_in_range CHECK (Mark BETWEEN 0 AND 100);

Using RENAME in combination with JOIN - SQL Example gives pairs of ids of students having the same name, by joining two renamings of IS_CALLED. Example gives an equivalent ex

Effect of Anonymous Columns Now, recall that a VALUES expression denotes a table with undefined column names. If an initial value is to be specified when a base table is creat

Keyword and Parameter Description: boolean_expression: This is an expression which results the Boolean value TRUE, FALSE, & NULL. It is related with a series of statement

The SQL ‘CREATE TABLE' scripts for all the tables you have implemented. Note that your tables must correspond exactly to the ERD you have provided in 1. above, or you will lose ma

ROWID and UROWID Internally, every database table has a ROWID pseudo column that stores binary values known as rowids. Each rowid shows the storage address of a row. A physical

OUT Mode An OUT parameter returns values to the caller of a subprogram. Within the subprogram, an OUT parameter act like a variable. That means that you can use an OUT formal