Deepika S Verma

• Apr 4th, 2006

 

According to O Reilly Book , the main difference between Index-By Table(pl-Sql Table) , Varray and nested tables are -

	Index-By Table	Nested Table	VARRAY
Dimensionality	Single	Single	Single
Usable in SQL?	No	Yes	Yes
Usable as column datatype in a table?	No	Yes; data stored "out of line" (in separate table)	Yes; data stored "in line" (in same table)
Uninitialized state	Empty (cannot be null); elements undefined	Atomically null; illegal to reference elements	Atomically null; illegal to reference elements
Initialization	Automatic, when declared	Via constructor, fetch, assignment	Via constructor, fetch, assignment
In PL/SQL, elements referenced via	BINARY_INTEGER (-2,147,483,647 .. 2,147,483,647)	Positive integer between 1 and 2,147,483,647	Positive integer between 1 and 2,147,483,647
Sparse?	Yes	Initially, no; after deletions, yes	No
Bounded?	No	Can be extended	Yes
Can assign value to any element at any time?	Yes	No; may need to EXTEND first	No; may need to EXTEND first, and cannot EXTEND past upper bound
Means of extending	Assign value to element with a new subscript	Use built-in EXTEND procedure (or TRIM to condense), with no predefined maximum	EXTEND (or TRIM), but only up to declared maximum size
Can be compared for equality?	No	No	No
Retains ordering and subscripts when stored in and retrieved from database?	N/A	No	Yes

Sravan Anumala

• Apr 13th, 2006

 

Varry and Nestead table both are belongs to CollectionsThe Main difference is Varray has Upper bound, where as Nestead table doesn't. It's size is unconstrained like any other database tableNestead table can be stored in DatabaseSyntax of Nestead TableTYPE nes_tabtype IS TABLE OF emp.empno%type;nes_tab nes_tabtype;Syntax of VarryTYPE List_ints_t IS VARRAY(8) OF NUMBER(2);aList List_ints_t:=List_ints_t(2,3,5,1,5,4);

Ganesh Ashok Korde

• Feb 9th, 2007

 

Nested table can be indexed where as VArray can't.

g_sidhu

 Profile 

• Feb 18th, 2008

 

Nested tables are unbounded.

Initially dense but can become sparse through deletions.

Order is not preserved

Can be indexed

 

VARRAYs are always bounded(varying arrays have a limited number of entries)

Never sparse.

When you store and retrieve a VARRAY, its element order is preserved.

Cannot be indexed

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 7th, 2008

 

The following are the main differences between nested tables and varray

1. Nested tables are unbounded where as varray has maximun limit.
2. Nested tables are dense intially but can be sparse latter.  So you can delete individual elements from nested tables. Varrays are always dense .It is not possible to delete individual elements in varrays.
3. Varrays stores data in-line. Nested tables are stored  seperately , main table  maintains a pointer to nested table.
4. When stored in database varrays retain their order where as nested tables do not.