DB: 12.1.0.2
OS: Linux, kernal 2.6.32
( Database is on a closed, secure network so I am re-typing this information - hopefully no typos. )
I have a question about adding a partition to a range-partitioned Index Organized table.
DDL:
CREATE TABLE table1_iot
(col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
CONSTRAINT table1_pk
PRIMARY KEY (col1, col2) )
ORGANIZATION INDEX NOLOGGING
PCTFREE 3 PARALLEL (DEGREE 4)
PARTITION BY RANGE (col1)
(PARTITION part01 VALUES LESS THAN (xxxxxxxxx) NOLOGGING TABLESPACE ts01 ,
PARTITION part02 VALUES LESS THAN (xxxxxxxxx) NOLOGGING TABLESPACE ts02 ,
PARTITION part03 VALUES LESS THAN (xxxxxxxxx) NOLOGGING TABLESPACE ts03 ,
PARTITION part04 VALUES LESS THAN (xxxxxxxxx) NOLOGGING TABLESPACE ts04 ,
PARTITION part05 VALUES LESS THAN (xxxxxxxxx) NOLOGGING TABLESPACE ts05 ,
PARTITION part06 VALUES LESS THAN (xxxxxxxxx) NOLOGGING TABLESPACE ts06 ,
PARTITION part07 VALUES LESS THAN (xxxxxxxxx) NOLOGGING TABLESPACE ts07 ,
PARTITION part08 VALUES LESS THAN (xxxxxxxxx) NOLOGGING TABLESPACE ts08 ,
PARTITION part09 VALUES LESS THAN (xxxxxxxxx) NOLOGGING TABLESPACE ts09 ,
PARTITION part10 VALUES LESS THAN (xxxxxxxxx) NOLOGGING TABLESPACE ts10 ,
PARTITION part11 VALUES LESS THAN (xxxxxxxxx) NOLOGGING TABLESPACE ts11 ,
PARTITION part12 VALUES LESS THAN (MAXVALUE) NOLOGGING TABLESPACE ts12 );
Each partition has around 900,000,000 records (900 million). Total size of table is around 12 billion records.
I add a 13th partition.
select max(col1) from table1_iot;
14000000000 {14 billion}
ALTER TABLE table1_iot SPLIT PARTITION part12 AT (14000000001) INTO
( PARTITION part12,
PARTITION part13 LOGGING TABLESPACE ts13 );
The above command took 9 hours to complete.
I had a copy of the same data in a range partitioned table that was not Index Organized. Doing the same task of splitting the last partition into two partitions at a point greater than the last record in the table, took less than 5 seconds. So my question is why does it take so, so much longer with an Index Organized table?