5 Replies Latest reply: Dec 29, 2011 4:54 PM by damorgan RSS

    Partitioned IOT of Object Type - mapping table not allowed for bitmap index

    908083
      Hi,

      looks like a feature available for standard Partitioned IOTs is not supported for object based tables, namely the MAPPING TABLE construct to support secondary local bitmap indexes.

      Can you confirm behaviour is as expected/documented?

      If so, is a fix/enhancement to support mapping table for object-based Partitioned IOTs in the pipeline?

      Results for partition-wise load using pipelined table function are very good, look-ups across tens of millions of rows are excellent.

      Environment = Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      OS = Oracle Enterprise Linux Server release 5.2 (Carthage) 2.6.18 92.el5 (32-bit)

      Here's the potted test-case...

      1) First the non object based Partitioned IOT - data is range-partitioned across the alphabet

      CREATE TABLE IOT_Table (
      textData VARCHAR2(10),
      numberData NUMBER(10,0),
      CONSTRAINT IOT_Table_PK PRIMARY KEY(textData))
      ORGANIZATION INDEX MAPPING TABLE PCTFREE 0 TABLESPACE Firewire
      PARTITION BY RANGE (textData)
      (PARTITION Text_Part_A VALUES LESS THAN ('B') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_B VALUES LESS THAN ('C') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_C VALUES LESS THAN ('D') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_D VALUES LESS THAN ('E') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_E VALUES LESS THAN ('F') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_F VALUES LESS THAN ('G') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_G VALUES LESS THAN ('H') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_H VALUES LESS THAN ('I') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_I VALUES LESS THAN ('J') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_J VALUES LESS THAN ('K') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_K VALUES LESS THAN ('L') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_L VALUES LESS THAN ('M') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_M VALUES LESS THAN ('N') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_N VALUES LESS THAN ('O') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_O VALUES LESS THAN ('P') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_P VALUES LESS THAN ('Q') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_Q VALUES LESS THAN ('R') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_R VALUES LESS THAN ('S') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_S VALUES LESS THAN ('T') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_T VALUES LESS THAN ('U') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_U VALUES LESS THAN ('V') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_V VALUES LESS THAN ('W') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_W VALUES LESS THAN ('X') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_X VALUES LESS THAN ('Y') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_Y VALUES LESS THAN ('Z') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_Z VALUES LESS THAN (MAXVALUE) PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0))
      NOLOGGING PARALLEL -- FLASHBACK ARCHIVE IOT_Flashback_Data
      ;

      SQL> table IOT_TABLE created.

      2) Create the local secondary bitmap index utilising the underlying mapping table

      CREATE BITMAP INDEX IOT_Table_BMI1 ON IOT_Table (numberData)
      LOCAL STORAGE (INITIAL 1M PCTINCREASE 0 NEXT 512K) NOLOGGING PARALLEL;

      SQL> bitmap index IOT_TABLE_BMI1 created.

      3) Quick test to confirm all ok

      SQL> INSERT INTO IOT_Table VALUES ('ABC123',100);

      SQL> 1 rows inserted.

      SQL> SELECT * FROM IOT_Table;

      TEXTDATA NUMBERDATA
      ABC123     100


      4) Now create a minimal object type to use as the template for object table

      CREATE TYPE IOT_type AS OBJECT
      (
      textData VARCHAR2(10 CHAR),
      numberData NUMBER(10,0)
      ) FINAL
      /

      SQL> TYPE IOT_type compiled

      5) Attempt to create an object-based range partitioned IOT, including MAPPING TABLE clause as per step (1)

      CREATE TABLE IOTObj_Table OF IOT_type (textData PRIMARY KEY)
      OBJECT IDENTIFIER IS PRIMARY KEY ORGANIZATION INDEX
      MAPPING TABLE -- we'd like to use this feature to enable use of Bitmap Indexes...
      PCTFREE 0 TABLESPACE Firewire
      PARTITION BY RANGE (textData)
      (PARTITION Text_Part_A VALUES LESS THAN ('B') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_B VALUES LESS THAN ('C') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_C VALUES LESS THAN ('D') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_D VALUES LESS THAN ('E') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_E VALUES LESS THAN ('F') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_F VALUES LESS THAN ('G') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_G VALUES LESS THAN ('H') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_H VALUES LESS THAN ('I') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_I VALUES LESS THAN ('J') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_J VALUES LESS THAN ('K') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_K VALUES LESS THAN ('L') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_L VALUES LESS THAN ('M') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_M VALUES LESS THAN ('N') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_N VALUES LESS THAN ('O') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_O VALUES LESS THAN ('P') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_P VALUES LESS THAN ('Q') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_Q VALUES LESS THAN ('R') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_R VALUES LESS THAN ('S') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_S VALUES LESS THAN ('T') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_T VALUES LESS THAN ('U') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_U VALUES LESS THAN ('V') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_V VALUES LESS THAN ('W') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_W VALUES LESS THAN ('X') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_X VALUES LESS THAN ('Y') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_Y VALUES LESS THAN ('Z') PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0),
      PARTITION Text_Part_Z VALUES LESS THAN (MAXVALUE) PCTFREE 0 TABLESPACE Firewire Storage (Initial 10M Next 1M PCTIncrease 0))
      NOLOGGING PARALLEL -- FLASHBACK ARCHIVE IOT_Flashback_Data
      ;

      This errors out with the following...

      SQL Error: ORA-25182: feature not currently available for index-organized tables
      25182. 00000 - "feature not currently available for index-organized tables"
      *Cause:    An attempt was made to use one or more of the following feature(s) not
      currently supported for index-organized tables:
      CREATE TABLE with LOB/BFILE/VARRAY columns,
      partitioning/PARALLEL/CREATE TABLE AS SELECT options,
      ALTER TABLE with ADD/MODIFY column options, CREATE INDEX
      *Action:   Do not use the disallowed feature(s) in this release.

      6) Re-running the create table statement in step 5 without the MAPPING TABLE clause works fine. Not surprisingly an attempt to create a secondary local bitmap index on this table fails as there's no mapping table, like so...

      CREATE BITMAP INDEX IOTObj_Table_BMI1 ON IOTObj_Table (numberData)
      LOCAL STORAGE (INITIAL 1M PCTINCREASE 0 NEXT 512K) NOLOGGING PARALLEL;

      CREATE TABLE with LOB/BFILE/VARRAY columns,
      partitioning/PARALLEL/CREATE TABLE AS SELECT options,
      ALTER TABLE with ADD/MODIFY column options, CREATE INDEX
      *Action:   Do not use the disallowed feature(s) in this release.

      -------------------------------------------------------------------------------
      CREATE BITMAP INDEX IOTObj_Table_BMI1 ON IOTObj_Table (numberData)
      LOCAL STORAGE (INITIAL 1M PCTINCREASE 0 NEXT 512K) NOLOGGING PARALLEL
      Error at Command Line:99 Column:13
      Error report:
      SQL Error: ORA-00903: invalid table name
      00903. 00000 - "invalid table name"

      7) Creating a secondary local b-tree index is fine, like so...

      SQL> CREATE INDEX IOTObj_Table_I1 ON IOTObj_Table (numberData)
      LOCAL STORAGE (INITIAL 1M PCTINCREASE 0 NEXT 512K) NOLOGGING PARALLEL;

      index IOTOBJ_TABLE_I1 created.

      8) A quick test to ensure object table ok...

      SQL> INSERT INTO IOTObj_Table VALUES (IOT_Type('DEF456',500));

      SQL> 1 rows inserted.

      SQL> SELECT * FROM IOTObj_Table;

      TEXTDATA NUMBERDATA
      DEF456     500
        • 1. Re: Partitioned IOT of Object Type - mapping table not allowed for bitmap index
          damorgan
          You are asking to PARTITION BY RANGE (textData) but how would you define the range?
          textData VARCHAR2(10 CHAR),
          numberData NUMBER(10,0)
          I can't ... and I suspect Oracle can't either.
          • 2. Re: Partitioned IOT of Object Type - mapping table not allowed for bitmap index
            908083
            Thanks Dan,

            the intention is to range partition based on the initial character, so A* -> Text_Part_A, B* -> Text_Part_B, and so on.

            Here's an example, using an empty IOTObj_Table as created previously.

            1) Set up & confirm some test data (two 'D's, one 'N', and two 'Z's)

            SQL> INSERT INTO IOTObj_Table VALUES (IOT_Type('DEF456',500));
            SQL> INSERT INTO IOTObj_Table VALUES (IOT_Type('DDD111',510));
            SQL> INSERT INTO IOTObj_Table VALUES (IOT_Type('N3000',515));
            SQL> INSERT INTO IOTObj_Table VALUES (IOT_Type('ZZ1212',520));
            SQL> INSERT INTO IOTObj_Table VALUES (IOT_Type('Z111X',530));
            SQL> COMMIT;

            SQL> SELECT * FROM IOTObj_Table;

            TEXTDATA NUMBERDATA
            DDD111     510
            DEF456     500
            N3000     515
            Z111X     530
            ZZ1212     520

            2) Just to prove our IOT is enforcing the Primary Key based on the TextData attribute, try to insert a duplicate

            SQL> INSERT INTO IOTObj_Table VALUES (IOT_Type('Z111X',530));

            Error starting at line 141 in command:
            INSERT INTO IOTObj_Table VALUES (IOT_Type('Z111X',530))
            Error report:
            SQL Error: ORA-00001: unique constraint (OCDataSystems.SYS_IOT_TOP_84235) violated
            00001. 00000 - "unique constraint (%s.%s) violated"
            *Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
            For Trusted Oracle configured in DBMS MAC mode, you may see
            this message if a duplicate entry exists at a different level.
            *Action:   Either remove the unique restriction or do not insert the key.

            3) Now confirm that our data has been slotted into the range-based partition we expect using the PARTITION clause of SELECT...

            - The two 'D's...

            SQL> SELECT * FROM IOTObj_Table PARTITION (Text_Part_D);

            TEXTDATA NUMBERDATA
            DDD111     510
            DEF456     500

            - The single 'N'...

            SQL> SELECT * FROM IOTObj_Table PARTITION (Text_Part_N);

            TEXTDATA NUMBERDATA
            N3000     515

            - The two 'Z's...

            SQL> SELECT * FROM IOTObj_Table PARTITION (Text_Part_Z);

            TEXTDATA NUMBERDATA
            Z111X     530
            ZZ1212     520

            4) And to wrap up confirm an empty partition

            SELECT * FROM IOTObj_Table PARTITION (Text_Part_W);
            • 3. Re: Partitioned IOT of Object Type - mapping table not allowed for bitmap index
              damorgan
              It appears the error message was correct so my recommendation would be to follow Tom Kyte's advise ... store relationally and use object-relational views.
              • 4. Re: Partitioned IOT of Object Type - mapping table not allowed for bitmap index
                908083
                That's fine, I'll hope the enhancement is on the wish list for next release.

                To wrap up I believe we have...

                CREATE VIEW IOTObj_View OF IOT_Type WITH OBJECT OID (TextData) AS SELECT TextData, NumberData FROM IOT_Table;

                Explain Plans for typical queries look great, FLASHBACK ARCHIVE and COMPRESSION (just OLTP for now) also useful

                SELECT * FROM IOTObj_View WHERE TextData = 'ABC123' AND NumberData = 20;

                SELECT * FROM IOTObj_View WHERE TextData LIKE 'ABC%' AND NumberData = 100;

                Once again thanks for your help Dan
                • 5. Re: Partitioned IOT of Object Type - mapping table not allowed for bitmap index
                  damorgan
                  Based on what I know ... which isn't much ... don't expect to see that "enhancement" in 12.1 or any other version.

                  It would take more than one very major customer demanding this functionality for it to be worth the effort required to implement it.