How To Estimate Oracle Text Index Fragmentation ?

is Oracle text index Fragmented ?


SYS AS SYSDBA>set linesize 200
select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where INDEX_TYPE='DOMAIN'



INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------- --------------------------------------------------------------------------------- ---------------
SYSOBJECT_FULLTEXT DOMAIN DM_SYSOBJECT_S



Text I

SYS AS SYSDBA>select table_name,OWNER from dba_tables where table_name like 'DR$SYS%';

or

select table_name,OWNER from dba_tables where table_name like '%SYSOBJECT_FULLTEXT%'


SYS AS SYSDBA>select table_name,owner from dba_tables where table_name like '%SYSOBJECT_FULLTEXT%' ;

TABLE_NAME OWNER
------------------------------ ---------------
DR$SYSOBJECT_FULLTEXT$P PROD1
DR$SYSOBJECT_FULLTEXT$I PROD1 <<<--- we have to use this table
DR$SYSOBJECT_FULLTEXT$K PROD1
DR$SYSOBJECT_FULLTEXT$R PROD1
DR$SYSOBJECT_FULLTEXT$N PROD1




select avg(tfrag) from ( select /*+ ORDERED USE_NL(i) INDEX(i DR$TEXT_IDX$X) */ i.token_text,
(1-(least(round((sum(dbms_lob.getlength(i.token_info))/3800)+(0.50 - (1/3800))),count(*))/count(*)))*100 tfrag
from ( select token_text, token_type from prod1.dr$SYSOBJECT_FULLTEXT$i sample(0.149)
where rownum <= 100 ) t, prod1.dr$SYSOBJECT_FULLTEXT$i i
where i.token_text = t.token_text
and i.token_type = t.token_type
group by i.token_text, i.token_type);



AVG(TFRAG)
----------
12.6856968




if result is more then 40 % the your index is fragmented .