5 Replies Latest reply: Apr 10, 2013 11:31 PM by 860675 RSS

    Index fragmentation

    Vadim Loevski
      Hi ,

      Is this the right query to determine index fragmentation ?

      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 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);

      The reason I am asking that before index rebuild it returned 86% but after rebuild (ALTER INDEX .. REBUILD) it returned
      96% which does not make sense.

      I did try ctx_report.index_stats but it takes more time to run.

      Thanks in advance

      Vadim
        • 1. Re: Index fragmentation
          Ebalthes-Oracle
          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) */
          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 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
          );
          • 2. Re: Index fragmentation
            Vadim Loevski
            Hi,


            Tha's exactly the query I supplied in my posting. My question was actually why this query returns more fragmentation after index rebuild.

            Regards,

            Vadim
            • 3. Re: Index fragmentation
              Vadim Loevski
              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 ?
              • 4. Re: Index fragmentation
                Roger Ford-Oracle
                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.
                • 5. Re: Index fragmentation
                  860675
                  step -1

                  analyze index t_idx validate structure;


                  step -2

                  select partition_name, lf_rows from index_stats;