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)
CANDIDATE_NO NOT NULL 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.