Index-Organized tables and Space Usage

From: Knut E. Meidal <knut_meidal_at_email.com>
Date: 20 Mar 2003 18:26:05 -0800
Message-ID: <64c6c1ea.0303201826.548e1bb2@posting.google.com>

I recently got the Kyte book, and read up on tables. Index-organized tables caught my eye, and especially the suggestion that they may save some space on the server. "Excellent", I thought, "lemme see how this works."

I took an existing heap table "filedetailcolumn" , and created an Index-Organized Table "iot1" defined like this: CREATE TABLE iot1 (

   FILE_ID NUMBER(8) NOT NULL,
   FILERECORD_ID NUMBER(8) NOT NULL,
   COLUMN_TYPE_ID NUMBER(8) NOT NULL,
   PRIOR_DAY DATE NOT NULL,
   VALTIME DATE NOT NULL,
   DST_FLAG NUMBER(1) NOT NULL,
   VALUE NUMBER(28, 8) NOT NULL,
   STATUS NUMBER(1),
   SETTLEDAY DATE,
PRIMARY KEY(FILE_ID,
   FILERECORD_ID,
   COLUMN_TYPE_ID,
   PRIOR_DAY,
   VALTIME,
   DST_FLAG ))
organization index
compress 3
pctfree 10
logging
TABLESPACE iot
including VALUE
overflow
pctfree 10
pctused 40
tablespace iotoverflow

The primary key in iot1 is the same as the original table. (The original table is very similar, but a normal heap table)

The IOT and IOTOVERFLOW tablespaces were created as locally managed, with uniform extent size 4M.

Next,
insert into iot1 (select * from filedetailcolumn) The table had about 14million rows.

When I later did a

	select segment_name, sum(bytes)/1024/1024 from user_segments
	group by segment_name

I saw that the space used reported something like this:

FILEDETAILCOLUMN 740MB
XPFILEDETAILCOLUMN 618MB
SYS_IOT_TOP_39217 1855MB
SYS_IOT_OVER_39217 231MB A data volume of 1358MB grows to 2096.
So much for saving space...

Any insights on why my Index-organized table is bloated compared to the original?
Is my "COMPRESS 3" directive at fault? PCTFREE parameters? INCLUDING? Is the B*Tree structure growing more quickly than the "heap table + Primary Key" structure?

Any insights appreciated

Knut Meidal Received on Thu Mar 20 2003 - 20:26:05 CST