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
Example of NOT EXISTS Operator - SQL Example is a translation into SQL of the corresponding example, which is included there merely to show that for any scalar comparison the

I need a query for PL/SQL, selecting names with cursor, goes down the list, assigns usernames (initials001) based on initials in the name. If two names have same initials the user

Definition of FROM - SQL Recall that the operand of FROM is denoted by a commalist, each element of that commalist being a table expression optionally accompanied by a range v

Use Bulk Binds If SQL statements execute inside a loop using the collection elements as bind variables, context switching between the PL/SQL & SQL engines can slow down the ex

Closing a Cursor Variable The CLOSE statement disables the cursor variable. After that, the related result set is undefined. The syntax for the same is as shown below: CLOS

FETCH Statement The FETCH statement retrieve rows of data one at a time from the result set of the multi-row query. The data is stored in fields or variables which correspond t

User-Defined Exceptions The PL/SQL defines the exceptions of your own. Dissimilar to the predefined exceptions, the user-defined exceptions should be declared and should be rai

Majority of Differences among 9i, 10G, 11G :- These are some combine feature which has differences among others. Automatic Workload Repository (AWR) Drop database' s

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

ALTER TABLE bb_basketitem ADD CONSTRAINT bitems_qty_ck CHECK (quantity BEGIN INSERT INTO bb_basketitem VALUES (88,8,10.8,21,16,2,3); END; Brewbean’s wants to add a check