This discussion is archived
9 Replies Latest reply: Feb 13, 2013 4:02 AM by user575115 RSS

IOT issue

user575115 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    thx

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points