This discussion is archived
14 Replies Latest reply: Apr 9, 2013 7:08 AM by riedelme RSS

real time scenario  and Advantages - Collections

user4295847 Newbie
Currently Being Moderated
Hi ,

Please provide the real time scenario for below collection. Which suitvations can we use the each collections ?
what are the advantages for each collection ?

1) Index-By-Tables
2) Varray
3) Nested tables
  • 1. Re: real time scenario  and Advantages - Collections
    Aman.... Oracle ACE
    Currently Being Moderated
    user4295847 wrote:
    Hi ,

    Please provide the real time scenario for below collection. Which suitvations can we use the each collections ?
    what are the advantages for each collection ?

    1) Index-By-Tables
    2) Varray
    3) Nested tables
    This should help,
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS005

    Aman....
  • 2. Re: real time scenario  and Advantages - Collections
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    1) Index-By-Tables
    Indexes are useful when

    o you want to access a small percentage of the rows in a table, say less then 5% for a
    small table and less then 15% for a larger table.

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:742997903057
    2) Varray
    3) Nested tables
    NESTED TABLE is an Oracle data type used to support columns containing multivalued attributes, in this case, columns that can hold an entire sub-table.

    You can check the below :
    http://docstore.mik.ua/orelly/oracle/prog2/ch19_01.htm
  • 3. Re: real time scenario  and Advantages - Collections
    user4295847 Newbie
    Currently Being Moderated
    Hi All ,

    I do not want any reference link.

    Please provide the explanation two or three lines as per your knowledge/experience.
  • 4. Re: real time scenario  and Advantages - Collections
    user4295847 Newbie
    Currently Being Moderated
    This is not help for me. please give me the explanation as per in your mind.
  • 5. Re: real time scenario  and Advantages - Collections
    Aman.... Oracle ACE
    Currently Being Moderated
    Well, so let's hear first from you that how much you understand about these terms? Explain in your words and we shall take it further from there.

    Aman....
  • 6. Re: real time scenario  and Advantages - Collections
    user4295847 Newbie
    Currently Being Moderated
    Hi ,

    I understand that , we can use the index-by-table for bulk-update/insert/delete in application development and nested tables, varray can hold the data in database.

    My question is ...what are the advantages for using nested table , varray ? why should use of them instead of storing the data diretly in to the table ?
  • 7. Re: real time scenario  and Advantages - Collections
    Aman.... Oracle ACE
    Currently Being Moderated
    user4295847 wrote:
    Hi ,

    I understand that , we can use the index-by-table for bulk-update/insert/delete in application development and nested tables, varray can hold the data in database.

    My question is ...what are the advantages for using nested table , varray ? why should use of them instead of storing the data diretly in to the table ?
    Did you read about them in teh documentation which you refused to read? These are datatypes which are going to work in the same way like Arrays work in the other languages. So now tell us why Arrays are better than creating standalone variable declarations?

    Aman....
  • 8. Re: real time scenario  and Advantages - Collections
    user4295847 Newbie
    Currently Being Moderated
    we can store the mutiple values in the collections variable...pls explain more...
  • 9. Re: real time scenario  and Advantages - Collections
    Aman.... Oracle ACE
    Currently Being Moderated
    user4295847 wrote:
    we can store the mutiple values in the collections variable...pls explain more...
    We can create 100's of variables too for the same thing so what's the benefit in declaring a collection variable?

    Aman....
  • 10. Re: real time scenario  and Advantages - Collections
    saratpvv Journeyer
    Currently Being Moderated
    what are the advantages for using nested table , varray ?
    Nested table have the advantage of being indexed, and the repeating groups are separated into another table so as not to degrade the performance of full-table scans.
    Nested Tables versus Index-by Tables
    
    Index-by tables and nested tables are similar. For example, they have the same structure, and their individual elements are accessed in the same way (using subscript notation). The main difference is that nested tables can be stored in a database column (hence the term "nested table") but index-by tables cannot.
    
    Nested tables extend the functionality of index-by tables by letting you SELECT, INSERT, UPDATE, and DELETE nested tables stored in the database. (Remember, index-by tables cannot be stored in the database.) Also, some collection methods operate only on nested tables and varrays. For example, the built-in procedure TRIM cannot be applied to index-by tables.
    
    Another advantage of nested tables is that an uninitialized nested table is atomically null (that is, the table itself is null, not its elements), but an uninitialized index-by table is merely empty. So, you can apply the IS NULL comparison operator to nested tables but not to index-by tables.
    
    However, index-by tables also have some advantages. For example, PL/SQL supports implicit (automatic) datatype conversion between host arrays and index-by tables (but not nested tables). So, the most efficient way to pass collections to and from the database server is to use anonymous PL/SQL blocks to bulk-bind input and output host arrays to index-by tables.
    
    Also, index-by tables are initially sparse. So, they are convenient for storing reference data using a numeric primary key (account numbers or employee numbers for example) as the index.
  • 11. Re: real time scenario  and Advantages - Collections
    Aman.... Oracle ACE
    Currently Being Moderated
    saratpvv wrote:
    what are the advantages for using nested table , varray ?
    Nested table have the advantage of being indexed, and the repeating groups are separated into another table so as not to degrade the performance of full-table scans.
    Nested Tables versus Index-by Tables
    
    Index-by tables and nested tables are similar. For example, they have the same structure, and their individual elements are accessed in the same way (using subscript notation). The main difference is that nested tables can be stored in a database column (hence the term "nested table") but index-by tables cannot.
    
    Nested tables extend the functionality of index-by tables by letting you SELECT, INSERT, UPDATE, and DELETE nested tables stored in the database. (Remember, index-by tables cannot be stored in the database.) Also, some collection methods operate only on nested tables and varrays. For example, the built-in procedure TRIM cannot be applied to index-by tables.
    
    Another advantage of nested tables is that an uninitialized nested table is atomically null (that is, the table itself is null, not its elements), but an uninitialized index-by table is merely empty. So, you can apply the IS NULL comparison operator to nested tables but not to index-by tables.
    
    However, index-by tables also have some advantages. For example, PL/SQL supports implicit (automatic) datatype conversion between host arrays and index-by tables (but not nested tables). So, the most efficient way to pass collections to and from the database server is to use anonymous PL/SQL blocks to bulk-bind input and output host arrays to index-by tables.
    
    Also, index-by tables are initially sparse. So, they are convenient for storing reference data using a numeric primary key (account numbers or employee numbers for example) as the index.
    Please cite the source of the text that you have quoted here.
    http://docs.oracle.com/cd/A91202_01/901_doc/appdev.901/a89856/05_colls.htm

    Aman....
  • 12. Re: real time scenario  and Advantages - Collections
    riedelme Expert
    Currently Being Moderated
    user4295847 wrote:
    Hi ,

    Please provide the real time scenario for below collection. Which suitvations can we use the each collections ?
    what are the advantages for each collection ?

    1) Index-By-Tables
    Index-by tables are very easy to set up and use. Just define them and you can use them. The index can be a string or a number, and you can use negative numbers - a neat trick if you are storing error messages with negative numbers as subscripts. Index-by tables work very well as sparsely populated collecitons where the index is being used for direct access instead of looping through every element one by one.
    2) Varray
    I personally have no use for VARRAYS in PL/SQL. I have not found anything a PL/SQL VARRAY can do that a nested table can't, and nested tables don't have the absolute size limiitation VARRAYS do. In the database VARRAYs are stored in order possibly avoiding a sort operation but I also prefer not to use VARRAYS or NESTED TABLES in the database because the SQL to reference the data is complex and using master-detail tables is easier.
    3) Nested tables
    Nested tables are slightly more efficient than index-by tables and work a little better when looping from the first to the last element in the collection. Nested tables require a little bit more work to set up and use than index-by tables requiring intialization and possibly the extend method to create new "buckets" (both happen automatically if populating the NESTED TABLE with a BULK COLLECT).
  • 13. Re: real time scenario  and Advantages - Collections
    jgarry Guru
    Currently Being Moderated
    I'll[url http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8135488196597]never use a nested table in a CREATE TABLE statement. You spend all of your time UN-NESTING them to make them useful again!
  • 14. Re: real time scenario  and Advantages - Collections
    riedelme Expert
    Currently Being Moderated
    jgarry wrote:
    I'll[url http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8135488196597]never use a nested table in a CREATE TABLE statement. You spend all of your time UN-NESTING them to make them useful again!
    Yup. There must be someone, somewhere who disagrees though - there always is :(

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points