Re: Index Space Usage more than Data Space Usage

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 Received on Wed Sep 11 2002 - 19:46:04 CDT