1 Reply Latest reply: Apr 25, 2014 5:23 PM by Barbara Boehmer RSS

    Optimize_Index in Oracle Text

    user13780543


      Hi All,

       

      I have tried the option Optimize_index in oracle text and it removed the un-necessary data from the $I table.

       

      I have used the Sub-String option in my index, so it created the $P table. During optimize_index, only the $I table gets cleaned up and the data in the $P remains the same.

       

      Can anyone suggest the way, how the other tables except $I which are created by text index can be optimized.

       

      Thanks,

      Loganathan

        • 1. Re: Optimize_Index in Oracle Text
          Barbara Boehmer

          I think you have to resort to rebuilding the index, as demonstrated below or drop the index and recreate it.

           

          SCOTT@orcl12c> create table test_tab

            2    (id      number,

            3      test_col  varchar2(60))

            4  /

           

          Table created.

           

          SCOTT@orcl12c> insert all

            2  into test_tab values (1, 'optimization test')

            3  into test_tab values (2, 'testing another')

            4  select * from dual

            5  /

           

          2 rows created.

           

          SCOTT@orcl12c> begin

            2    ctx_ddl.create_preference ('test_wordlist', 'basic_wordlist');

            3    ctx_ddl.set_attribute ('test_wordlist', 'substring_index', 'true');

            4  end;

            5  /

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl12c> create index test_idx on test_tab (test_col)

            2  indextype is ctxsys.context

            3  parameters ('wordlist test_wordlist sync (on commit)')

            4  /

           

          Index created.

           

          SCOTT@orcl12c> insert all

            2  into test_tab values (3, 'testing optimization')

            3  into test_tab values (4, 'another test')

            4  select * from dual

            5  /

           

          2 rows created.

           

          SCOTT@orcl12c> commit

            2  /

           

          Commit complete.

           

          SCOTT@orcl12c> delete from test_tab where id in (2, 4)

            2  /

           

          2 rows deleted.

           

          SCOTT@orcl12c> select count(*) from dr$test_idx$i

            2  /

           

            COUNT(*)

          ----------

                   8

           

          1 row selected.

           

          SCOTT@orcl12c> select count(*) from dr$test_idx$p

            2  /

           

            COUNT(*)

          ----------

                  22

           

          1 row selected.

           

          SCOTT@orcl12c> begin

            2    ctx_ddl.optimize_index ('test_idx', 'rebuild');

            3  end;

            4  /

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl12c> select count(*) from dr$test_idx$i

            2  /

           

            COUNT(*)

          ----------

                   3

           

          1 row selected.

           

          SCOTT@orcl12c> select count(*) from dr$test_idx$p

            2  /

           

            COUNT(*)

          ----------

                  22

           

          1 row selected.

           

          SCOTT@orcl12c> alter index test_idx rebuild

            2  /

           

          Index altered.

           

          SCOTT@orcl12c> select count(*) from dr$test_idx$i

            2  /

           

            COUNT(*)

          ----------

                   3

           

          1 row selected.

           

          SCOTT@orcl12c> select count(*) from dr$test_idx$p

            2  /

           

            COUNT(*)

          ----------

                  17

           

          1 row selected.