Index Space Usage more than Data Space Usage

Index Space Usage more than Data Space Usage

Post by Or » Wed, 11 Sep 2002 14:08:52



I administer an OLTP database.

I have separate tablespaces on different Disk for Table data and index
Objetcts .

FOr one Module, the Space used in Index Tablespace is almost double
than that of the corresponding Data Tablespace.

Is it possible that Index objects occupy such large space vis-a-visa
its Table Data  ?

OrA

 
 
 

Index Space Usage more than Data Space Usage

Post by Sybrand Bakke » Wed, 11 Sep 2002 15:16:29



Quote:> I administer an OLTP database.

> I have separate tablespaces on different Disk for Table data and index
> Objetcts .

> FOr one Module, the Space used in Index Tablespace is almost double
> than that of the corresponding Data Tablespace.

> Is it possible that Index objects occupy such large space vis-a-visa
> its Table Data  ?

> OrA

This is quite normal

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address

 
 
 

Index Space Usage more than Data Space Usage

Post by Joe Sat » Wed, 11 Sep 2002 20:37:55


If you delete a lot of data, the entry in index will not be deleted, that is
why index will take more space than the data it self.
Alter index rebuild will solve the problem.



> > I administer an OLTP database.

> > I have separate tablespaces on different Disk for Table data and index
> > Objetcts .

> > FOr one Module, the Space used in Index Tablespace is almost double
> > than that of the corresponding Data Tablespace.

> > Is it possible that Index objects occupy such large space vis-a-visa
> > its Table Data  ?

> > OrA

> This is quite normal

> Hth

> --
> Sybrand Bakker
> Senior Oracle DBA

> to reply remove '-verwijderdit' from my e-mail address

 
 
 

Index Space Usage more than Data Space Usage

Post by Kenneth Koenraad » Wed, 11 Sep 2002 20:54:19




Quote:>If you delete a lot of data, the entry in index will not be deleted, that is
>why index will take more space than the data it self.

Nope.
If you delete a lot of rows in a table, the table itself will not
shrink either (unless you truncate).  So deleting a lot of data in a
table will not itself cause an index to be bigger than the table.

Quote:>Alter index rebuild will solve the problem.

Not necessarily. Also depends on the storage parameters you give the
index.

- Kenneth Koenraadt





>> > I administer an OLTP database.

>> > I have separate tablespaces on different Disk for Table data and index
>> > Objetcts .

>> > FOr one Module, the Space used in Index Tablespace is almost double
>> > than that of the corresponding Data Tablespace.

>> > Is it possible that Index objects occupy such large space vis-a-visa
>> > its Table Data  ?

>> > OrA

>> This is quite normal

>> Hth

>> --
>> Sybrand Bakker
>> Senior Oracle DBA

>> to reply remove '-verwijderdit' from my e-mail address

 
 
 

Index Space Usage more than Data Space Usage

Post by Richard Foot » Wed, 11 Sep 2002 20:57:33


Hi Ora,

Yes it's quite possible for an index tablespace to take up more space than
it's corresponding table tablespace (as indeed it's possible for an
individual index to take up more space than it's parent table).

Note that an index has all the intermediate pages as "overhead" as well as
storing the 10 byte rowid for each non null row. Also an index may not be
too efficient in reusing deleted space for incrementally increasing values.
Then of course there's always the issue of inappropriate PCTFREE and
depending on what you mean by "used" space, there's inappropriate storage
clause values that could cause grief. Last (and certainly not least) you may
have many indexes on the same table, many of which could use the same column
values. The sum of all these columns could easily exceed the sum of the
columns on a table.

It is an indication that the overheads associated with your indexes may be a
bit OTT and it might be worth some investigation.

Cheers

Richard

Quote:> I administer an OLTP database.

> I have separate tablespaces on different Disk for Table data and index
> Objetcts .

> FOr one Module, the Space used in Index Tablespace is almost double
> than that of the corresponding Data Tablespace.

> Is it possible that Index objects occupy such large space vis-a-visa
> its Table Data  ?

> OrA

 
 
 

Index Space Usage more than Data Space Usage

Post by Or » Thu, 12 Sep 2002 15:50:27


Thank You all for your responses.

I shall investigate the objects in the Index Table spaces.

Affly
OrA

 
 
 

Index Space Usage more than Data Space Usage

Post by Or » Fri, 13 Sep 2002 09:46:04


I further investigated and found many indexes occupying more spaces
than Table Data.

Out of this I am  giving below one of the  most disproportionate Table
called INVITATION_DUTY . The Table Data used space is 50 Mb where as
the Index  space is four times higher at over 200 MB.

The table has 6 fields of which 4 are NOT NULL.  Each row occupies
less than 60 bytes.

The table has one Primary Key Constraint and 4 foreign Key constraints
(indexes ) as given below.

Table Name : INVITATION_DUTY

Table Structure :
ID_NO                  NOT NULL  VARCHAR2(12)
INVT_REF_NO            NOT NULL  VARCHAR2(12)
STATUS_CODE            NOT NULL  VARCHAR2(2)
STATUS_DATE                          DATE
CANDIDATE_NO           NOT NULL  NUMBER(5)
SL_NO                            NUMBER(5)

Table Data used Size :  50.875 MB

Index Names and Size:
FRMREPE013_PK                   56.71875 MB
FRMREPE013_FRMREPE019_FK_I      50.78125 MB      
FRMREPE013_FRMINVE005_FK_I      47.5 MB
FRMREPE013_FRMPKCE002_FK_I      32.0625 MB
FRMREPE013_MISISCE053_FK_I      28.125 MB

Total Index used Size :             215.1875 MB

Please comment on the above ?

The data block size is 4 KB.

Both the Table  and Index tablespaces are Locally Managed (LMT)

Is there a way to find out the empty space  within each index blocks.
?

Affly
OrA