-
Hi Friends,
I have a very large table, which has only two columns - one is a VARCHAR2(15) and the other is
NUMBER. The primary key of this table consists of both columns. In addition to the index created
for the primary key, I have also created a separate index (non-unique) on the second column. Here
are the specifics:
Table A: 367001600 bytes; 18600797 rows
Primary key index: 524288000 bytes
Index on column 2: 367001600 bytes
The way I understand indexes, they are essentially storing the same data in the column(s) being
indexed, in sorted order. This makes me wonder about a few things in the above example:
1. Why is the index on column 2 the same size (in bytes) as the original table? If it is only
storing the data from column 2 (sorted), shouldn't it be smaller than the table, which has two
columns worth of data?
2. Why is the primary key index quite a bit larger than the original table? Since this index is on
both columns of the table, I would think that it would be storing the same data as the table does,
just sorted in a different order. Shouldn't this index and the table be the same size?
3. In this case, where I have a primary key (index) that contains all fields of the table that it
is indexing, it seems wasteful to store this data twice. After all, if the primary key index stores
the same data that is in the table, but sorted, what is the use of the original table? Is there
some way to combine the table and the index into one "entity" so that this data is only stored in
the database once?
Thanks for any insights on this matter.
sahib
-
2.
Your index is bigger then you table because in the index-blocks the ROWID is also stored.
So,
in the table-blocks you have VARCHAR2(15)+Number
in the index-blocks you have ROWID+VARCHAR2(15)+Number
1.
Not sure but,
may be the space occupied by the ROWID in the index, equals
the space occupied by the VARCHAR2(15) in the table ???
3.
use an Index Organized Table
but, it depends on your version if you will be able to make a second index on that IOT.
Hope this helps.
Gert
-
Hi,
How did you decide the space occupied by the index (in bytes)?
Ac
