9 Replies Latest reply: Feb 13, 2013 6:02 AM by user575115 RSS

    IOT issue

    user575115
      Hi,

      Initially IOT tale created with 3 columns and PK has created on these 3 columns.Now one more new column added in IOT table i.e WITHHOLD_ALL .Now i need to create PK all on these 4 columns.To do this i was tried to drop existing PK but it throwing can not drop IOT pk constraint.can you some advice how i can proceed.
       CREATE TABLE org_table 
         (     "YEAR" NUMBER(4,0) NOT NULL ENABLE, 
           "MONTH" VARCHAR2(9 CHAR) NOT NULL ENABLE, 
           "CANDIDATE_CODE" VARCHAR2(6 CHAR) NOT NULL ENABLE, 
           "WITHHOLD_ALL" VARCHAR2(1 CHAR) DEFAULT 'N', 
            CONSTRAINT "WITHHOLD_CAND_PK" PRIMARY KEY ("YEAR", "MONTH", "CANDIDATE_CODE") ENABLE
         ) ORGANIZATION INDEX COMPRESS 2 PCTFREE 0 INITRANS 2 MAXTRANS 255 LOGGING
        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
        TABLESPACE "DATAONE" 
       PCTTHRESHOLD 50 OVERFLOW
       PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
        TABLESPACE "DATATWO" ;
        • 1. Re: IOT issue
          Karthick_Arp
          As far as i know you need to drop the table and re create it fresh.

          Something like this
          -- Consider T as your table with pkey ID
          create table t
          (
            id integer primary key,
            name varchar2(10) 
          ) 
          organization index;
          
          insert into t
          select level, 'A' || level
            from dual
          connect by level <= 10; 
          
          commit;
          
          -- Now you want to add ID, Name to the pkey
          -- So you create a temp table 
          create table t_temp
          as
          select *
            from t;
          
          -- Drop the original table
          drop table t;  
          
          -- Recreate the table with additional pkey columns
          create table t
          (
            id integer,
            name varchar2(10),
            constraint t_pk primary key (id, name)
          ) 
          organization index;
          
          -- Insert the values from the temp table
          insert into t 
          select * 
            from t_temp;
          
          -- Drop the temp table  
          drop table t_temp;  
          • 2. Re: IOT issue
            Frank Kulash
            Hi,

            Create a new index-organized table, with all 4 columns in the primary key.
            Populate the new table with data from the original table (I believe you called it ORG_TABLE).
            Drop the original ORG_TABLE.
            Rename the new table to ORG_TABLE.
            • 3. Re: IOT issue
              APC
              Are you certain you want to add what appears ro be a yes/no flag to that table's primary key? It is permitted to have columns on IOTs which aren't part of the PK (although more than one would be unusual).

              Cheers, APC
              • 4. Re: IOT issue
                Etbin
                Drop and recreate a table after transferring data to a temporary table.
                Insert data back from the temporary table and drop the temporary table.
                an IOT is actually an index containing the data as well. Ther's no separate pk index, that's why you cannot drop it.

                Regards

                Etbin
                • 5. Re: IOT issue
                  Etbin
                  although more than one would be unusual
                  Why? Just being curious.
                  I'm usually obliged to have the overflow data segment (including column_name ...) one or several columns seems to make little difference.
                  The important thing is those columns are pretty rarely used when querying the IOT.

                  Regards

                  Etbin
                  • 6. Re: IOT issue
                    APC
                    Etbin wrote:
                    although more than one would be unusual
                    Why?
                    I'm usually obliged to have the overflow data segment (including column_name ...) one or several columns seems to make little difference.
                    The important thing is those columns are pretty rarely used when querying the IOT.
                    Etbin

                    I guess we're going to quiblle over the definition of "unusual".

                    The use cases for IOTs are pretty niche to start with: ref data look-ups and the like. Generally there aren't many multi-column tables where the only access is through the primary key and no other path except a full table scan. Which is not the same as "none".

                    I'd be interested to know what your use cases are (if you can spill the beans).

                    Cheers, APC
                    • 7. Re: IOT issue
                      Etbin
                      Nothing special really.
                      It's just that by some (insert any pejorative) internal "rules" columns as created by (user), created by (organizational unit), last updated by (user), last updated by (organizational unit), change id (sequence value), ... maybe others (I try not to remember all of them - copy/paste is enough to adhere to) are mandatory.
                      Sometimes a large comments column is required (it seldom gets filled and someone gets interested in only every pope's death). That's usually a reason others don't want to make the table index organized, although the table is used exclusively to provide a single row or a range scan of them.
                      You can by all means call this situation unusual but here it is and I have to deal with.

                      Regards

                      Etbin
                      • 8. Re: IOT issue
                        rp0428
                        >
                        I guess we're going to quiblle over the definition of "unusual".

                        The use cases for IOTs are pretty niche to start with: ref data look-ups and the like. Generally there aren't many multi-column tables where the only access is through the primary key and no other path except a full table scan. Which is not the same as "none".

                        I'd be interested to know what your use cases are (if you can spill the beans).
                        >
                        Hey - are you two having a private 'quiblle' or can anyone jump in and stir things up?
                        >
                        Are you certain you want to add what appears ro be a yes/no flag to that table's primary key? It is permitted to have columns on IOTs which aren't part of the PK (although more than one would be unusual).
                        >
                        I'd argue just the opposite; having only ONE non-PK column would be unusual.

                        An IOT is a table and I rarely run use, or see others use, a table with only two columns. In the U.S. a state lookup table might have

                        1. STATE_SEQ - Primary key and used for referential integrity
                        2. ABBREVIATION (e.g. CA)
                        3. STATE_NAME (e.g. Californiak)
                        4. Several other pieces of data that might be used: date joined the union, state flag, state flower, etc

                        The data is stored in the leaves, only keys are stored in the branches.

                        Many small tables, particularly lookups, can benefit by being IOTs instead of a heap table and an index. I submit that it 'would be unusual' to use all but one column in the key.
                        • 9. Re: IOT issue
                          user575115
                          thx