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 .

Oracle Text Index statistics report / fragmentation report

Here is an example of using CTX_REPORT.INDEX_STATS for text Index


create this table
create table output (result CLOB);

or

if already there

truncate table OUTPUT ;

from sys


prompt create table output (result CLOB);

truncate table OUTPUT ;

declare
x clob := null;

begin

ctx_report.index_stats('PROD1.SYSOBJECT_FULLTEXT',x);
insert into output values (x);
commit;
dbms_lob.freetemporary(x);
end;
/



set this line to read clob

spool text_stat_report

SET LONG 1000000 LONGC 1000000 LIN 80 TIMI ON
select * from output;

spool off