5

I know basic difference and uses of both. But, what I'm looking for as an answer is, why exactly VARRAY introduced ?

Since, we can do same thing using NESTED TABLE what can be done using VARRAY whereas vice-versa is not possible in some scenario. Also, I have noticed some places people mentioned VARRAY stores elements in-line. What does it mean ? Can anyone explain internal storage and processing of both ?

Ravi
  • 30,829
  • 42
  • 119
  • 173

3 Answers3

2

To summarize, you would use a Variable-Size Arrays a.k.a VARRAY when:

  • Number of elements are known before populating it.
  • Elements need to be accessed sequentially
  • Guaranteed order of elements

For example, a VARRAY is declared as:

TYPE varray_emp IS VARRAY(14) OF emp%ROWTYPE;
emp_rec varray_emp;

So, you see the UPPER BOUND is fixed, in the above example it is 14.

See the documentation for more details.

Update Regarding storing VARRAY in database

Quoting from the above documentation link:

Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB). You must update or retrieve all elements of the varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way.

Regarding in line storage:

A varray is normally stored in line, that is, in the same tablespace as the other data in its row. If it is sufficiently large, Oracle stores it as a BLOB

Read more about Storage Considerations for Varrays

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • I know what you have answered above. What I actually looking for is, how they are getting stored ? what does in-line storage mean ? – Ravi Apr 22 '15 at 06:16
  • See the update regarding the storage of VARRAY and in line storage meaning. – Lalit Kumar B Apr 22 '15 at 06:35
  • In-line storage mean, the values are stored like simple values, e.g. numbers or (short) text. Outline means, the values are stored in an extra table, this table can be even in another tablespace and thus even on different physical hard disc, although the data belongs to a single record in your table. – Wernfried Domscheit Apr 22 '15 at 13:36
  • Agreed. But, regarding the numbers, internally they are again stored as varying-size characters. And, when the row-size increases sufficiently due to the VARRAY, it is handled the same way as a BLOB. – Lalit Kumar B Apr 22 '15 at 13:45
2

There are 2 main important differences:

  1. Maximum number of elements (limit) is needed in VARRAY declaration. Limit is not possible in nested TABLE type collection.

  2. Inline store possible in VARRAY (nested TABLE is always out of line)

    If collection type is used as normal table column type in database:

    Out off line - nested table data are stored in different database segment than main table rows. For nested table segment type is NESTED TABLE.

    Inline - nested rows stored inside main table row.

    For VARRAY CLOB is used. Database will store LOBs less than +-4000 bytes of data in the table column. https://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_tables.htm#i1010742

SlawekCpw
  • 21
  • 2
-1

Difference b/w nested table and varray:

Separate Table Space will be created for nested table which is other than the parent table's table space. If a varray size is less than 4 KB, it is stored inside the table of which it is a column otherwise, it is stored outside the table but in the same table space.
Possible to perform update, delete on the individual element of nested table.
It is not possible to perform updates and deletes on the individual elements in a Varray. For More clarification go through tht below link. http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/05_colls.htm

Tharunkumar Reddy
  • 2,773
  • 18
  • 32