How to Estimate Text Index Fragmentation Level?
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 text_user.dr$text_idx$i sample(0.149)
where rownum <= 100 )
t, text_user.dr$text_idx$i i
where i.token_text = t.token_text
and i.token_type = t.token_type
group by i.token_text, i.token_type
);
what is dr in the script
where index_owner=TB
index name=TB_IDX
request your help to append this information in the script