Summary:
In a prior blog, “A Guide to Maintaining Your Search Indexes for WebCenter Content,” we provided a script for determining the level of fragmentation in a WCC search index. This script, however, can take hours, if not days to complete on very large content repositories. It is not uncommon for the average enterprise customer’s search index table rows to exceed several hundred thousand rows. In addition to scanning the large row count, complex calculations are performed to deem the overall percentage of fragmentation based on expected post-optimization database optimization results.
Solution:
Use the below statement for roughly estimating your index fragmentation, here index owner is text_user and index name is text_idx:
- The index name can be found under WCC Administration → Configuration.
Script
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
);
Note: It is important to run this script during a low-usage period of your environment.
Per Oracle, index fragmentation should not be above 40%. Users are likely to experience performance issues at fragmentation levels above 40%. See our more definitive guide on resolving fragmentation here.
Is your organization struggling to keep up with content demands? Contact Inspired ECM to learn how we can optimize or improve upon your existing WebCenter solution.
