Database Administration (MOSC)

MOSC Banner

How to Estimate Text Index Fragmentation Level?

edited Jun 1, 2015 10:02AM in Database Administration (MOSC) 1 commentAnswered

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

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center