TEXT Index Size

Oracle Text Index, an interMedia Index, is powerful for searching user specified text within a column or columns. It is widely used in web applications such as search engines, content management systems. As a DBA, accurately identify sizes of text indexes in the database and monitor their growth is important. But the problem is that text index is a domain index, and every text index internally consists of several tables with names prefixed with 'DR$'. Some of these tables have their own indexes and some of them are Index Organized Tables (IOTs). If you search in dba_segments, there is no segment for text indexes. SO when you try to calculate the size of an text index, you should consider all of these DR$ tables and their indexes. The script provided here gives a fast report of the sizes of all text indexes in the schema:

COL table_name format A30;
COL ind_nm format A30;
COL KB 9999999999;

select table_name, x.index_name, sum(KB) KB from
(select substr(table_name, 4, instr(table_name, '$', -1)-4) index_name,
sum(bytes)/1024 KB
from user_tables t, user_segments s
where t.table_name = s.segment_name and t.table_name like 'DR$%$%'
group by substr(table_name, 4, instr(table_name, '$', -1)-4)
union
select substr(table_name, 4, instr(table_name, '$', -1)-4) index_name,
sum(bytes)/1024 KB from user_indexes i, user_segments s
where i.index_name = s.segment_name and i.table_name like 'DR$%$%'
group by substr(table_name, 4, instr(table_name, '$', -1)-4)
) x, user_indexes ind
where x.index_name = ind.index_name
group by table_Name, x.index_name
order by table_name, x.index_name;

Related Topics:

1. Introduction of Oracle Text
2. Oracle Text Installation