2 Replies Latest reply: Aug 7, 2010 1:14 AM by BelMan RSS

    Nested Table Partitioning in 11gR2

    437130
      [ Partitioning Concepts for 11gR2|http://download.oracle.com/docs/cd/E11882_01/server.112/e10837/partition.htm] says that Oracle will partition the nested table of a partitioned parent table, automatically:

      "If you partition a table that has a nested table, Oracle Database uses the partitioning scheme of
      the original base table as the basis for how the nested table is partitioned. This partitioning of
      one base table partition for each nested table partition is called equipartitioning. By default,
      nested tables are automatically partitioned when the base table is partitioned."

      Here is some DDL to create a partitioned table with a nested table column:

      CREATE OR REPLACE TYPE ExampElement AS OBJECT (name VARCHAR2(32), val VARCHAR2(4000))
      /
      sho err;

      CREATE OR REPLACE TYPE ExampTableType AS TABLE OF ExampElement
      /
      sho err;


      CREATE TABLE examp (
      uida VARCHAR2(64) NOT NULL,
      idx NUMBER NOT NULL,
      offset SMALLINT DEFAULT -1 NOT NULL ,
      elements ExampTableType,
      CONSTRAINT pk_examp PRIMARY KEY (uida, idx) )
      ORGANIZATION INDEX COMPRESS
      PARTITION BY LIST(uida) (PARTITION IDA_0 values ('xxxxxxxxxxxxxxx') )
      NESTED TABLE elements
      STORE AS elements_tab((constraint pk_elts PRIMARY KEY (nested_table_id, name))
      ORGANIZATION INDEX
      )
      RETURN AS LOCATOR
      /
      sho err;


      I can't find any evidence that Oracle partitioned my nested table. The following do not show any entries corresponding to elements_tab or pk_elts:

      SELECT table_name FROM user_part_tables ;
      SELECT table_name FROM user_part_indexes ;


      My question is, how can I prove to myself that the nested table has been partitioned?


      Thanks,

      Hugh
        • 1. Re: Nested Table Partitioning in 11gR2
          Barbara Boehmer
          The only data dictionary entry that I can find for the elements_tab is in user_nested_tables, but it does not appear that the partitioning is visible to us.
          SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE ExampElement AS OBJECT (name VARCHAR2(32), val VARCHAR2(4000));
            2  /
          
          Type created.
          
          SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE ExampTableType AS TABLE OF ExampElement;
            2  /
          
          Type created.
          
          SCOTT@orcl_11gR2> CREATE TABLE examp (
            2  uida VARCHAR2(64) NOT NULL,
            3  idx NUMBER NOT NULL,
            4  offset SMALLINT DEFAULT -1 NOT NULL ,
            5  elements ExampTableType,
            6  CONSTRAINT pk_examp PRIMARY KEY (uida, idx) )
            7  ORGANIZATION INDEX COMPRESS
            8  PARTITION BY LIST(uida) (PARTITION IDA_0 values ('xxxxxxxxxxxxxxx') )
            9  NESTED TABLE elements
           10  STORE AS elements_tab((constraint pk_elts PRIMARY KEY (nested_table_id, name))
           11  ORGANIZATION INDEX
           12  )
           13  RETURN AS LOCATOR
           14  /
          
          Table created.
          
          SCOTT@orcl_11gR2> SELECT table_name FROM user_part_tables
            2  /
          
          TABLE_NAME
          ------------------------------
          EXAMP
          
          1 row selected.
          
          SCOTT@orcl_11gR2> SELECT table_name FROM user_part_indexes
            2  /
          
          TABLE_NAME
          ------------------------------
          EXAMP
          
          1 row selected.
          
          SCOTT@orcl_11gR2> SELECT table_name FROM user_tab_partitions
            2  /
          
          TABLE_NAME
          ------------------------------
          EXAMP
          
          1 row selected.
          
          SCOTT@orcl_11gR2> SELECT table_name FROM user_nested_tables
            2  /
          
          TABLE_NAME
          ------------------------------
          ELEMENTS_TAB
          
          1 row selected.
          
          SCOTT@orcl_11gR2>
          • 2. Re: Nested Table Partitioning in 11gR2
            BelMan
            see this post
            http://asktom.oracle.com/pls/asktom/f/f?p=100:11:0::::P11_QUESTION_ID:2420065900346364349