14 Replies Latest reply: Apr 9, 2013 9:08 AM by riedelme RSS

    real time scenario  and Advantages - Collections

    user4295847
      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....
          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
            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
              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
                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....
                  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
                    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....
                      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
                        we can store the mutiple values in the collections variable...pls explain more...
                        • 9. Re: real time scenario  and Advantages - Collections
                          Aman....
                          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
                            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....
                              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
                                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
                                  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
                                    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 :(