14 January 2011

Nuances of Nested Tables (1864)

The 13 January quiz tested your knowledge of nested tables, one of the three types of collections available in PL/SQL (two of which, nested tables and varrays, can be manipulated within SQL).

In response to this quiz, one player wrote: "I am a bit confused about nested tables. As a DBA I know that nested tables are schema objects in the database, but it what we used to call PLSQL tables are now also called nested tables? In my opinion (assuming that I am correct here, which is not necessarily the case of course), this makes today's quiz a bit ambiguous: you can use SQL statements to access nested tables that exist in the database, but not to access PLSQL tables, I believe. It is not clear which type are meant."

To clarify: the datatype formerly known as the "PL/SQL table" is now known as an associative array. It is a PL/SQL-only datatype. That is, you cannot define an associative array type as a schema level type (a.k.a, database object); you cannot use that type as a column in a relational table; you cannot manipulate an associative array with the TABLE operator - all of which you can do with nested tables and varrays. And, interestingly, even if you declare a nested table as a PL/SQL variable, you can manipulate in an SQL statement as long as the type on which it is declared is a schema-level type.

Another player wrote: "Today's quiz about nested tables is not clear. What does it mean "A nested table can have as many elements in it as a relational table has rows"? Example: I'm trying to create a straightforward column with 1001 columns of type NUMBER... it fails. I'm trying to create a straightforward table with 1 column of user type which is not final. The type has many final implementations. Once the total number of virtual columns hit 1000 it fails to insert any new data. I'm trying to create a nested table which has 1001 columns, thus 1001 elements or attributes... wouldn't it fail? Now let's assume that "element" gets redefined into "tuple" or "row". I can insert more than 1000 rows into the nested table. Finally: If I'm a stubborn being then shouldn't be the last answer true as well?"

To which I respond: an element in a collection is analogous to a row in a relational table. Which is to say that you can define and locate an element through its index value. I intended to test with the statement "A nested table can have as many elements in it as a relational table has rows" your awareness that there is an upper limit to the number of elements a nested table may contain (valid index values range from 1 to 2**31-1), while there is no such limit in an Oracle relational table.

32 comments:

  1. I, also, answered this question purely from the standpoint of nested tables as schema objects, since I consider table types in PL/SQL merely arrays.

    The question about number of "elements" was also confusing because I had to guess what an "element" is in that context.

    However, I got this question wrong primarily because I very rarely find a use for this feature - give me a sane, proper, simple relational model any day :)

    ReplyDelete
  2. From the Oracle10g PL/SQL User's Guide, under the section titled "What are PL/SQL Collections and Records?" you will find:

    "A collection is an ordered group of elements, all of the same type. It is a general
    concept that encompasses lists, arrays, and other datatypes used in classic
    programming algorithms. Each element is addressed by a unique subscript."

    ReplyDelete
  3. Hi Steven,

    I understand that there is limit on number of rows in PL/SQL nested table. Applies the same limit to the SQL nested table defined as column?

    In other words, isn't it (theoretically) possible to insert more than 2**31-1 rows with
    insert into table(select nested_table_column from some_table) values (...);?

    I am not able to find any comment in documentation about this.

    Regards,
    Michal Cvan

    ReplyDelete
  4. I am wondering if the 2**31-1 upper limit to the number of elements a nested table applicable to the nested table in PL/SQL or as well to the nested table in SQL (used as a column in the relational table)? If it is not applicable in latter case the "A nested table can have as many elements in it as a relational table can have rows." choice should be considered as correct, as from quiz' context is not clear that this choice relates to PL/SQL only - there is at least one choice related to SQL ("Nested table types can be used as datatypes for columns in relational tables.").

    Kind regards,
    Oleksandr

    ReplyDelete
  5. I disagree with your explanation for answer #3 in the quiz of Jan 13th. A nested table type can be created in SQL only. Using TYPE in a declaration section merely defines a type in PL/SQL, which is certainly a difference (e.g. more data types possible). A type definition in a package provides a type for use, but does not create it as a distinguishable element. To me, there is a difference between type creation and type definition, but I'm possibly mislead, not being an native English speaker.

    Kind regards,
    Peter Schmidt

    ReplyDelete
  6. Like Jeff Kemp I also answered the question from the standpoint of SQL, because inside the "PL/SQL User's Gude and Refernence" there is only one reference to "nested table type" and that's in a comment of an example.

    All other parts only references "collection types" and that's also my view on it: in SQL you have nested table types and varray types and in PL/SQL you have collection types.

    In my opinion you can also see the difference at using pipelined functions inside of PL/SQL packages because Oracle automatically generates corresponding nested table types for you and don't use the collection type defined in the package:

    SQL> SELECT Type_Name, TypeCode FROM User_Types;
    TYPE_NAME TYPECODE
    ------------------------ -----------
    TDBFILEINFORMATION OBJECT
    TDBTBLFILEINFORMATIONS COLLECTION

    SQL> CREATE OR REPLACE
    2 PACKAGE Test IS
    3 TYPE TtblNumbers IS TABLE OF NUMBER;
    4 FUNCTION PipelinedTest RETURN TtblNumbers PIPELINED;
    5 END Test;
    6 /
    Package wurde erstellt.

    SQL> CREATE OR REPLACE
    2 PACKAGE BODY Test IS
    3 FUNCTION PipelinedTest RETURN TtblNumbers PIPELINED
    4 IS
    5 BEGIN
    6 FOR ix IN 1..5 LOOP
    7 PIPE ROW (ix);
    8 END LOOP;
    9 RETURN;
    10 END PipelinedTest;
    11 END Test;
    12 /
    Package Body wurde erstellt.

    SQL> SELECT Type_Name, TypeCode FROM User_Types;
    TYPE_NAME TYPECODE
    -------------------------- ------------
    SYS_PLSQL_180058_DUMMY_1 COLLECTION
    SYS_PLSQL_180058_9_1 COLLECTION
    TDBFILEINFORMATION OBJECT
    TDBTBLFILEINFORMATIONS COLLECTION

    SQL> SELECT t.* FROM TABLE( Test.PipelinedTest()) t;
    COLUMN_VALUE
    ------------
    1
    2
    3
    4
    5

    SQL> SELECT Type_Name, TypeCode FROM User_Types;
    TYPE_NAME TYPECODE
    -------------------------- ------------
    SYS_PLSQL_180058_DUMMY_1 COLLECTION
    SYS_PLSQL_180058_9_1 COLLECTION
    TDBFILEINFORMATION OBJECT
    TDBTBLFILEINFORMATIONS COLLECTION

    SQL> DROP PACKAGE Test;
    Package wurde gelöscht.

    SQL> SELECT Type_Name, TypeCode FROM User_Types;
    TYPE_NAME TYPECODE
    ----------------------- ------------
    TDBFILEINFORMATION OBJECT
    TDBTBLFILEINFORMATIONS COLLECTION

    ReplyDelete
  7. Hi,

    With the clarification quiz question for the number of elements is OK. Without it it was misleading in my opinion. As a result I recommend score adjustment.
    Note that I gave here an answer which is counted as "correct".

    Question "a nested table can be ... ONLY ..." - this ONLY keyword should be uppercase/underlined/etc. and you should have clarified whether you are asking about PL/SQL or SQL within the question. Instead I see it as misleading.

    "You can use a SQL SELECT statement to retrieve values from a nested table defined on a schema-level nested table type."
    Using the same wording:
    "If, on the other hand" you create a column of type ANYDATA and put PL/SQL VARCHAR2(32767) into it... try to retrieve it using SQL which has the limitation to use max 4k :D
    Note that I gave here an answer which is counted as "correct".
    What is my point here: (1) avoid unclear questions and (2) avoid questions that rely on a single word which might been used in some context in some Oracle document. The first one is misleading the audience. The second one is good for lawyers to overuse the system for their benefit, not a common man.

    "Nested table types must be defined in the database as schema-level types."
    The same as above. Clarify if this is about SQL or PL/SQL.
    Note that I gave here an answer which is counted as "incorrect" - I was misled by the use of "must" and "be defined in the database". I'm using this quite frequently in PL/SQL. If you say that PL/SQL = "in the database" then I would answer that C and JAVA code can be run on it as well...

    And thanks again for a great quiz which is a great source of knowledge for me, just like your books are.

    ReplyDelete
  8. Some comments on the above comments:

    Nested table types can be defined/created as schema-level objects or as types within a PL/SQL block (or, more likely, a package). This is an important thing to be aware of. I deliberately did not specify which I was talking about in some of these choices for that reason.

    It I do not specify that I am talking about a schema-level type or a PL/SQL-based type, then it seems logical to me that you would not assume one or the other. I don't think that is the least bit misleading.

    As for the use of the word "create" meaning that I am talking about a schema-level type and not one defined in PL/SQL block, that seems like "hair-splitting" or "word-splitting" to me. I create a package that has within it a nested table type. I have therefore created a nested table type for use in my applications. You could just as well argue that I "defined" a schema level type as "created" it.

    Does the limit of 2**-1 rows apply to a nested table in a relational table column? I have never read anything that indicates otherwise. Has anyone else? It sure is a hard thing to test, but barring that I do not see how we cannot rely on the documented limits.

    ReplyDelete
  9. According to Oracle® Database Application Developer's Guide - Object-Relational Features
    10g Release 2 (10.2)
    , "A nested table can have any number of elements" (Section 3, "Support for Collection Datatypes"). Since the choice "A nested table can have as many elements in it as a relational table can have rows" did not specify PL/SQL nested tables, it is ambiguous and should be rescored.

    ReplyDelete
  10. Fascinating! Two responses:

    1. I wonder if this statement means that there is really no upper bound of elements allowed in a nested table that is a column in a table or Oracle is simply saying in comparison to varrays that you do not specify a maximum. I will see if I can find out. I don't really see how there a nested table can have more than 2**31-1 elements, since you can access elements by their index values.

    2. Even if the above is true, since I did not specify a nested table based on schema-level type or PL/SQL-based type, the choice would by default have to apply to both. It is not ambiguous - it is comprehensive. So I still do not see why I would rescore for this.

    ReplyDelete
  11. Hi Steven!

    I rather disagree with your points. If a nested table in the column of the relational table can have more than 2**31-1 element it just means that this table is not compatible with PL/SQL.Your choice does not say "is there an upper limit on the nested table size in PL-SQL", it refers to the limit without any qualification, such limit shall be applicable to any situation in which nested tables may be used in Oracle. If it can be broken at least in once case - it is not a limit and the choice must be rescored.

    Regards,
    Oleksandr

    ReplyDelete
  12. A very good point, Oleksander. If a nested table can under even one circumstance have as many rows as a relational table (that is, no limit) then I will have to rescore. I have sent a note to Bryn Llewellyn (PL/SQL Product Manager) asking for clarification. I do not believe that the interpretation presented on this blog of the statements in the document (while perfectly understandable) are correct. I do not believe that a nested table can be arbitrarily large. So I will wait to hear from Bryn and then take appropriate action.

    ReplyDelete
  13. Steven

    while waiting for a response from an Oracle guy..

    Ordinary PL/SQL developers usually don't have a friend working as a PL/SQL Product Manager in Oracle so they have to rely (and trust) Oracle documentation (and, of course, their own experience). If the documentation is not clear different people can interpret it in different ways. Which means - ambiguity. It doesn't matter what a PL/SQL Product Manager says now if the documentation was unclear before (we all took the quiz).. :)

    Regards
    Ludo

    ReplyDelete
  14. While I consider Bryn a friend, he is first and foremost the PL/SQL Product Manager. Which means, among other things, that anyone who is a PL/SQL developer should feel welcome (he has told me this) to contact him with questions about PL/SQL. His email address is bryn.llewellyn@oracle.com.

    You may find this hard to believe, but I don't get special treatment from Bryn. I just bother him a lot. :-)

    As to your point regarding documentation, I am torn. Generally, I would say that a problem in the documentation (and there are many) is not an "excuse" for making an incorrect choice - precisely because you could "always" write some code to determine "the truth."

    In this case, however, it would be extremely difficult to verify the reality. I have certainly never been able to populate a collection of any type with anything approaching 2**31-1 elements.

    So....I give up. :-) I will change this choice to specifically reference a PL/SQL nested table type, and give everyone credit for a correct choice.

    ReplyDelete
  15. Steven

    my comment about having a friend in Oracle wasn't meant to offend you. Sorry if it did (but there was smile at the end).

    What I meant to say was that we (ordinary PL/SQL Developers :-p ) usually use Oracle documentation as a study material, that should be the first thing to look to. We have to trust that things are as they are descibed there (well, sometimes it is hard to trust if you see funny thing like - http://download.oracle.com/docs/cd/B14117_01/server.101/b10755/statviews_1014.htm - look at description of POSITION column :D ). Yes, you are right, I could always write some code to determine the truth - but it means that I have to write code to test and verify every single thing - how could I know what is correct and what is not? :)

    I don't want you to rescore (even if I would benefit from it). I just wanted to point out that documentation should (must?) be (always) clear. I believe that argument "problem in the documentation is not an excuse" should not be applied here - if the question is related to a topic I have never used and I search the documentation and choose the answer based on it and then you say it is wrong because documentation is wrong - it lowers the solidity of the contest.

    I hope it makes sense what I want to say. I believe the quizzes should not be based on principle "ask first and then verify".

    Have a nice rest of the weekend :)

    Regards
    Ludo

    ReplyDelete
  16. This is a tough situation with no clear resolution, in my mind. There are many problems with the Oracle documentation (though that documentation is also in so many other ways quite wonderful and constantly improving), starting with a lack of clear definitions for many elements of the language.

    I definitely do not want to constrain quizzes to the vagaries and problems of documentation and I cannot commit to checking all possible places in documentation before publishing each quiz. I simply don't have the time. Perhaps I can find a volunteer who can make this their review task? :-)

    In any case, I have changed the scores and ranking.

    ReplyDelete
  17. Oh, and I was not offended. I just feel very strongly that any and all PL/SQL developers should be in touch with Bryn, to tell him how you use PL/SQL, how it should be improved, and issues you have with it.

    ReplyDelete
  18. "starting with a lack of clear definitions for many elements of the language"

    That's one of the benefits of this quiz, a place where interpretations of these terms can be opened up to a wider community.

    ReplyDelete
  19. Hello All,

    I think that the main problem of this choice resides in the fact that the statement
    DID NOT specify exactly whether it refers to:

    1. a nested table stored in a relational table
    2. a nested table type variable in PL/SQL
    3. both the above

    Strictly speaking, in the absence of this specification, it is natural to consider the statement as referring to both types ( that is, "comprehensive", as Steven called it above ).
    If so, then the original marking of the choice as incorrect was right.
    As Oleksandr said above, one single case that contradicts the statement renders it incorrect,
    but here the statement said that a nested table CAN (POSITIVE) have as many elements in it as a
    relational table can have rows.
    The case of a nested table type variable in PL/SQL clearly contradicts it, so the statement is NOT correct.

    On the other hand, since the statement itself mentions "relational table" (though as a comparison term only), and the general context of the quiz (and some other choices in it) did also deal with nested tables stored in database columns, some players might have interpreted the "nested table" in this choice as obviously referring to a nested table stored in a database column.
    This is the case for which WE DO NOT KNOW AT THIS POINT whether it is possible THEORETICALLY
    to store more than 2**31-1 elements (nested table rows) into it.

    The documentation (Oracle® Database Application Developer's Guide - Object-Relational Features)
    does say that "No maximum is specified IN THE DEFINITION of the table", but we cannot specify
    whether this definitely means that we can indeed execeed the 2**31-1 elements limit, because
    we will probably hit a physical limitation while trying to check it.
    Anyway, since such a number of child elements (in a nested table, that means, belonging all of them to one single parent row) will probably be of NO practical use, this might be a reason that neither
    the documentation nor the examples insist on this issue.

    A short remark also related to Niels Hecker's post above:

    Starting with Oracle11gR1, a nested table type defined in a PL/SQL package and used as a return type for a pipelined function IS NOT implemented any more by creating implicitly the schema level types named SYS_PLSQL%, as it was the case for Oracle9i and Oracle10g.
    Maybe some similar operation is performed internally using some kind of database types,
    but this is NOT visible any more in the DBA_TYPES dictionary table.

    Regarding our possibility to directly communicate with Oracle high level specialists,
    like both Steven as Bryn, we can and should only feel very grateful and honored of this.

    Thanks & Best Regards,
    Iudith

    ReplyDelete
  20. OK, it is really hard to test if we can insert more than 2**31-1 rows in the nested table when it is used as a value of a column in some relational table but it looks very plausible that we can:

    1. a nested table is stored as an relational table (STORE AS clause).
    2. Relational tables have no limit numbers on of rows (explicitly documented, see Logical Database Limits in Reference).
    2. we do not need to resort to PL/SQL to perform DML operations on such tables (see Table Collections: Examples in the SQL Reference).

    Kind regards,
    Oleksandr

    ReplyDelete
  21. Hello All,
    Just a short addition (another debt to Niels Hecker) to my previous post
    related to Oracle11g implementation of nested table types defined in a package spec and returned by a pipelined function:

    Though the types created are NOT visible in USER_TYPES, they are however visible in USER_COLL_TYPES.

    For example, after having created 2 such types,
    a TABLE OF NUMBER and a TABLE OF VARCHAR2(4000)
    I see the following:


    SQL> select type_name, typecode
    2 from user_types where type_name like 'SYS%'
    3 /

    no rows selected

    But
    SQL> select type_name, coll_type, elem_type_name
    2 from user_coll_types where type_name
    like 'SYS%'
    3 /

    TYPE_NAME COLL_TYPE
    ------------------------------ ------------------------------
    ELEM_TYPE_NAME
    ------------------------------
    SYS_PLSQL_223508_9_1 TABLE
    NUMBER

    SYS_PLSQL_223508_DUMMY_1 TABLE
    NUMBER

    SYS_PLSQL_223512_9_1 TABLE
    VARCHAR2

    SYS_PLSQL_223512_DUMMY_1 TABLE
    NUMBER


    4 rows selected.

    In Oracle9i and Oracle10g the types created inplicitly by SYS were visible in USER_TYPES also, so something (partially) changed.


    Oleksandr, related to your post:

    It is right that the data for a STORED nested table column is physically (and we can say partly that also logically) stored in a regular relational table, and this relational table of course DOES NOT have any theoretical limit on the number of rows .

    But these rows do belong to ALL the nested tables
    (for all the master rows) that are stored in the table that has the nested table column.

    More than this:

    I just checked out something for which Tom Kyte
    would surely kill me, therefore I just tell you about it, without specific code samples:

    There exists an undocumented feature by which you can retrieve data directly from the nested table STORAGE table, as if it were a simple relational table (this appears in Tom's "Expert One on one-Oracle" ).

    I just checked and found that you can not only select, but also INSERT data directly into the
    nested table's storage table !!!

    But that data DOES NOT belong to ANY parent row
    from the table containing the nested table column !!!
    That is, it CANNOT be retrieved by the usual documented methods, that is, the methods that use a TABLE function and always retrieve, or perform DML on ONE SINGLE nested table, belonging to one specific parent row.

    So, while the STORAGE relational table probably DOES NOT have a theoretical limit for its
    number of rows, I dare suppose that the INDIVIDUAL nested tables DO HAVE SUCH A LIMIT,
    though we cannot prove it (yet :) ?).
    I can try it, but I'm afraid I will make the database explode and they will fire me !!!

    I'm very eager to hear Steven and Bryn's further comments on this issue, any idea about how can we check it effectively ?
    If for example we will try to fill a nested table
    with 2**31 NULL values, and maybe define some form of compression on the nested table storage table (I never tried it !)
    are we in danger to make the database (or the archive files) explode ?

    Thanks & Best Regards,
    Iudith

    ReplyDelete
  22. This comment has been removed by the author.

    ReplyDelete
  23. >I dare suppose that the INDIVIDUAL nested tables DO HAVE SUCH A LIMIT, though we cannot prove it (yet :) ?).

    Actually no:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> set timing on
    SQL> create or replace type number_t is table of number(1);
    2 /

    Type created.

    Elapsed: 00:00:00.03
    SQL> create table tn (t number_t) pctfree 0 nologging compress
    2 nested table t store as tnn (pctfree 0 nologging compress);

    Table created.

    Elapsed: 00:00:00.09
    SQL> insert into tn values (number_t(null));

    1 row created.

    Elapsed: 00:00:00.04
    SQL> commit;

    Commit complete.

    Elapsed: 00:00:00.00
    SQL> begin
    2 for i in 0 .. 21474 loop
    3 insert /*+ append*/ into table(select t from tn) select null from dual connect by level <= 100000;
    4 commit;
    5 end loop;
    6 end;
    7 /

    Elapsed: 03:05:14.87
    PL/SQL procedure successfully completed.

    SQL> select 2147483647 pls_max, count(*) cnt from tn, table(t);

    PLS_MAX CNT
    ---------- ----------
    2147483647 2147500001

    Elapsed: 00:19:47.34
    SQL> select s.BYTES from user_segments s where s.segment_name = 'TNN';

    BYTES
    ----------
    2.4321E+10

    Elapsed: 00:00:00.01

    ReplyDelete
  24. @judith
    "But these rows do belong to ALL the nested tables
    (for all the master rows) that are stored in the table that has the nested table column." - definitely, but "unlimited/any_finite_number" is still unlimited. And at any point in time the master table contains only finite number of rows.

    As for feature that you mentioned - I am pretty aware of it - but even without this (as you properly said undocumented) feature you may manipulate (insert, update, delete) data in the nested table with usual DML operators. And this way is perfectly documented, my previous post mentioned it - but I would provide here a quote from Oracle SQL Reference:
    --------------------------------------
    CREATE TYPE people_typ AS OBJECT (
    last_name VARCHAR2(25),
    department_id NUMBER(4),
    salary NUMBER(8,2));
    /
    CREATE TYPE people_tab_typ AS TABLE OF people_typ;
    /
    CREATE TABLE hr_info (
    department_id NUMBER(4),
    location_id NUMBER(4),
    manager_id NUMBER(6),
    people people_tab_typ)
    NESTED TABLE people STORE AS people_stor_tab;

    INSERT INTO hr_info VALUES (280, 1800, 999, people_tab_typ());


    The following example inserts into the people nested table column of the hr_info table for department 280:

    INSERT INTO TABLE(SELECT h.people FROM hr_info h
    WHERE h.department_id = 280)
    VALUES ('Smith', 280, 1750);
    --------------------------------------

    Note, that INSERT...SELECT works as well.
    So with something like

    INSERT INTO TABLE(SELECT h.people FROM hr_info h
    WHERE h.department_id = 280)
    SELECT * FROM TABLE(
    SELECT h.people FROM hr_info h WHERE h.department_id = 280)

    Repeated in the loop we can reach 2*31 rows in the table in not so many loops - as each run doubles the number of rows in the nested table. The problem is only if we have enough free space in the DB.

    Regards,
    Oleksandr

    ReplyDelete
  25. Hello Oleksandr,
    Yes, the problem is NOT the loop here, but
    whether it will allow you or not to exceed the
    (2**31-1) limit of the number of elements,
    supposing that we DO have the necessary free
    space in the database, which we probably don't have, that is why we cannot check it effectively.

    If we look at a nested table definition,
    it is NOT different when we create a nested table
    at the schema level using CREATE TYPE
    or we create it using a TYPE declaration in a PL/SQL unit.
    Neither one of the 2 definitions specifies any limit regarding the number of elements,
    however, PL/SQL clearly does NOT allow more than
    (2**31-1) elements to be added to the nested table.
    Therefore, at least by this example, I would expect the database type to also enforce that THEORETIC limitation, regardless of the fact that the STORAGE table of the nested table can store any number of rows.
    Let's not forget that we can have an arbitrary
    high number of rows in the parent table,
    so, even if each nested table in each parent row
    only contains one single element, that is already an arbirtrary high number of rows in the nested table column's STORAGE table,
    but NOT in any of the NESTED TABLES themselves
    ( as individual objects ).
    So, I still am inclined to believe that the limit of (2**31-1) elements CANNOT be exceeded,
    even in a nested table stored in the database.

    What would happen for example if we define a NESTED TABLE type using CREATE TYPE (that is, a nested table that allows any number of elements)
    and then define a PL/SQL variable of that database type ?
    Will that variable "inherit" from its TYPE
    the same characteristic of being able to store ANY number of elements ?
    or
    Will it be subject to the PLS_INTEGER index size
    limitation of PL/SQL ?

    I think we will not be freed from this arguing
    except by one of the two:
    1.Either somebody will find a method to prove it one way or another.
    or
    2.One of the Oracle's core developers, that is aware of the internal implementation will supply us the precise answer, the one that is not so clearly concludable from the Documentation.

    Anyway, all this blog raised a very interesting point that I'm sure that most if not all of us would probably have never thought of :) :).

    Thanks & Best Regards,
    Iudith

    ReplyDelete
  26. A little test (improved version instead of my post at January 16, 2011 9:28 PM):

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> set timing on
    SQL> create or replace type number_t is table of number(1);
    2 /

    Type created.

    Elapsed: 00:00:00.03
    SQL>
    SQL> create table tn (t number_t) pctfree 0 nologging compress
    2 nested table t store as tnn (pctfree 0 nologging compress);

    Table created.

    Elapsed: 00:00:00.09
    SQL>
    SQL> insert into tn values (number_t(null));

    1 row created.

    Elapsed: 00:00:00.04
    SQL> commit;

    Commit complete.

    Elapsed: 00:00:00.00
    SQL>
    SQL> begin
    2 for i in 0 .. 21474 loop
    3 insert /*+ append*/ into table(select t from tn) select null from dual connect by level <= 100000;
    4 commit;
    5 end loop;
    6 end;
    7 /

    PL/SQL procedure successfully completed.

    Elapsed: 01:09:38.25
    SQL>
    SQL> select 2147483647 pls_max, count(*) cnt from tn, table(t);

    PLS_MAX CNT
    ---------- ----------
    2147483647 2147500001

    Elapsed: 00:13:49.65
    SQL>
    SQL> select s.bytes from user_segments s where s.segment_name = 'TNN';

    BYTES
    ----------
    2.4321E+10

    Elapsed: 00:00:00.07

    ReplyDelete
  27. Here is another small example:

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> set timing on
    SQL> create or replace type number_t is table of number(1);
    2 /

    Type created.

    Elapsed: 00:00:00.00
    SQL>
    SQL> create or replace function get3E9rows return number_t pipelined is
    2 i number := 3E9;
    3 begin
    4 while i > 0 loop
    5 pipe row (null);
    6 i := i - 1;
    7 end loop;
    8 end;
    9 /

    Function created.

    Elapsed: 00:00:00.01
    SQL>
    SQL> select count(*) from table(get3E9rows);

    COUNT(*)
    ----------
    3000000000

    Elapsed: 00:05:51.10

    ReplyDelete
  28. As _Nikotin provided the direct evidence that the limit in question is not applicable to the nested tables in the DB, the quiz needs to be be rescored.

    ReplyDelete
  29. 1. Congratulations and many thanks to _Nikotin for providing a simple script to prove that the documentation is correct: there is apparently no limit to the number of elements in a nested table stored as a table's column. I have added one of these scripts to the answer for the quiz.

    2. The rescoring is already done (I said I would do this earlier in the commentary: "So....I give up. :-) I will change this choice to specifically reference a PL/SQL nested table type, and give everyone credit for a correct choice.").

    What a wonderful and incredibly informative exploration into PL/SQL and related structures! Thanks so much to everyone for their participation and their assistance in deepening my knowledge in this area.

    ReplyDelete
  30. Hello All,

    A pipelined function IS NOT the same as a STORED nested table !!!

    Even if using a VARRAY ( which DOES have a limit defined, no arguing I hope :) :) ),
    if making that VARRAY type the return type of a pipelined function, then you can exceed the type's declared LIMIT !!!
    (Believe me or not, but it just happened to me to check this issue right BEFORE this quiz based on some task at my work, but deeply inspired by the PL/SQL Challenge spirit !!!).

    A pipelined function just returns COLLECTION ELEMENTS one by one, and NOT an entire collection !!!
    It just helps the SQL layer to "see" a seria of
    "fabricated rows" as if they are coming from a real relational table.

    This still DOES NOT prove the point, that is
    that in a nested table STORED (physically !!!)
    in the DATABASE you can exceed the 2**31-1 limit !!!

    Steven, YOU ARE REALLY GREAT !!!

    Did WE deepen your knowledge ???

    Then this is an HISTORICAL DAY for all the bloggers here !!!

    Maybe in a future version some score bonus will be afforded to the bloggers ?!?

    Thanks for your enthusiasm, I think we were all very enthusiastic about this issue and our knowledge is climbing higher and higher,
    though, don't forget, we still don't have a final proof and the swords are still out :) :) :)

    ReplyDelete
  31. It is interesting, that even when the SQL nested table does not have the limitation, PL/SQL engine converts it to own format.

    Let's use type from _Nikotins example:
    create or replace type number_t is table of number(1);

    Then this code will throw an exception "numeric owerflow":
    declare
    l_tab number_t := number_t();
    begin
    l_tab.extend(3E9);
    end;
    /

    This meas that in PL/SQL exists a limitation even if we use SQL type.

    Regards,
    Michal

    ReplyDelete
  32. iudith,

    Please see comment at "January 17, 2011 7:43 AM", not "January 18, 2011 1:05 PM"

    ReplyDelete