Re: Index-Organized tables and Space Usage

"Knut E. Meidal" wrote:

> 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

It is the size of the table plus the size of the indexes.

But more importantly is not reading large numbers of index blocks PLUS large numbers of table blocks.

Daniel Morgan Received on Thu Mar 20 2003 - 20:53:20 CST