This content has been marked as final. Show 5 replies
Index fragmentation is returned by ctx_report.index_stats procedure. If this takes too long then your index is highly fragmented.
Use below statement for estimating roughly your index fragmentation, here index owner is text_user and index name is text_idx:
select avg(tfrag) from
( select /*+ ORDERED USE_NL(i) INDEX(i DR$ctxnamesind_idx$X) */
(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 esys.dr$ctxnamesind_idx$i sample(0.149)
where rownum <= 100 )
t, esys.dr$ctxnamesind_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
Tha's exactly the query I supplied in my posting. My question was actually why this query returns more fragmentation after index rebuild.
I also just re created the index using DRPOP...CREATE and after running the query again the fragmentation was 80%. How is that possible. Is the index created already fragmented ?
Don't use ALTER INDEX REBUILD but instead use ctx_ddl.optimize_index in 'FULL' mode (or 'REBUILD' mode).
As mentioned in my earlier email, if you create the index with a high degree of parallelism and a small MEMORY parameter you will often find the index is fragmented after creation.
analyze index t_idx validate structure;
select partition_name, lf_rows from index_stats;