Issue with creating index organized table

Posted by mtim on Stack Overflow See other posts from Stack Overflow or by mtim
Published on 2010-03-25T19:38:37Z Indexed on 2010/03/25 20:03 UTC
Read the original article Hit count: 239

Filed under:

I'm having a weird problem with index organized table. I'm running Oracle 11g standard.

i have a table src_table

SQL> desc src_table;
 Name            Null?    Type
 --------------- -------- ----------------------------
 ID     NOT NULL   NUMBER(16)
 HASH       NOT NULL   NUMBER(3)
 ........

SQL> select count(*) from src_table;
  COUNT(*)
----------
  21108244

now let's create another table and copy 2 columns from src_table

set timing on
SQL> create table dest_table(id number(16), hash number(20), type number(1));
Table created.
Elapsed: 00:00:00.01

SQL> insert /*+ APPEND */ into dest_table (id,hash,type) select id, hash, 1 from src_table;
21108244 rows created.
Elapsed: 00:00:15.25

SQL> ALTER TABLE dest_table ADD ( CONSTRAINT dest_table_pk PRIMARY KEY (HASH, id, TYPE));
Table altered.
Elapsed: 00:01:17.35

It took Oracle < 2 min.

now same exercise but with IOT table

SQL> CREATE TABLE dest_table_iot (
       id     NUMBER(16) NOT NULL,
       hash  NUMBER(20) NOT NULL,
       type  NUMBER(1)  NOT NULL,
       CONSTRAINT dest_table_iot_PK PRIMARY KEY (HASH, id, TYPE)
    ) ORGANIZATION INDEX;

Table created.
Elapsed: 00:00:00.03

SQL> INSERT /*+ APPEND */ INTO dest_table_iot (HASH,id,TYPE)
    SELECT  HASH, id, 1 
    FROM src_table; 

"insert" into IOT takes 18 hours !!! I have tried it on 2 different instances of Oracle running on win and linux and got same results.

What is going on here ? Why is it taking so long ?

© Stack Overflow or respective owner

Related posts about Oracle