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

Fetching with a Cursor The FETCH statements retrieve the rows in the result set one at a time. After each and every fetch, the cursor advance to the next row in the result set

What are the rates for help in writing PL/SQL procedures and functions?

Keyword and Parameter Description: table_reference: This specifies a table or view that should be accessible when you execute the DELETE statement, and for that you must

Parameter Default Values As the illustration below shows, you can initialize the IN parameters to the default values. In that way, you can pass various numbers of actual par

EXECUTE Privilege To call an invoker-rights routine straightforwardly, the users should have the EXECUTE privilege on that routine. By yielding the privilege, you permit a user

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

Create a Oracle procedure to produce vertical output format when selecting rows from a database table.

Initial thought process: Design a script which was simple and user friendly. Integrate procedures/functions to extract data under the hood. I focused on giving the user the opt

SQL Cursor   The Oracle implicitly opens a cursor to process each SQL statement not related with an explicit cursor. The PL/SQL refers to the most current implicit cursor as t