|
Category :
Database
Resources -> Database Administration
DB Version
:
Oracle 8i /
Oracle 9i
OS Details :
Sun Solaris9
What
are some of the things that we need to be aware of when it comes to
Index Organized Tables (IOT)? IOTs store the data as if the entire
table was stored in an index. A normal index only stores the
indexed columns in the index; an IOT stores all of the table's columns
in the index. We will not go in to details about what an IOT is since
there are numerous online documentation on it. However, let's look at
some of the peripheral features of IOTs : Secondary Indexes and the
logical rowids.
What are logical rowids?
Logical rowids are associated
with the secondary indexes for an IOT and includes a physical guess
which identifies the block location of the row in the index-organized
table at the time the guess was made which was when the secondary
index was created or rebuilt.
How expensive are secondary
indexes?
Rowid from a normal heap
organized table takes up about 10 bytes of storage. However, a logical
rowid takes up more than 3 times that space. Here a quick check (abc
is an index organized table):
SQL> select vsize(rowid) from abc
where rownum=1;
VSIZE(ROWID)
-------------------
38
A logical rowid takes up about 38
bytes of space! Let's take a scenario of an IOT having about a million
rows and you want to build a secondary index on a column of size 10
bytes. The total index size would be more than 45MB and more than 3.5
MB of that space has been taken up just for the logical rowids! That's
something to keep in mind when you plan for secondary indexes on IOTs.
What else do we have to
worry about?
When oracle includes the
secondary indexes in its execution path, oracle can use guesses to
probe in to the leaf blocks directly, bypassing the full key search.
This ensures that rowid access of non-volatile index-organized tables
gives comparable performance to the physical rowid access method
adopted against ordinary tables. However, if an IOT has frequent DML
operations being performed on it then the guess becomes stale and the
probe will fail.
Therefore, one has to make sure
that for a volatile IOT, the secondary indexes are kept up to date and
can be done as follows:
SQL> alter index abc_sec_idx
update block references;
|
