3 Replies Latest reply: Jul 16, 2013 1:54 PM by Barbara Boehmer RSS

    Creating INDEX on BLOB Column in separate tablespace

    amostapha

      Hello,


      Our Database contains 2 tablespaces :

      -  Tablespace DATA : is reserved to contain the data.

      -  Tablespace INDX: is reserved to contain the indexes.


      For some reasons we should create the index on blob columns, and the current command is :

      SQL> CREATE INDEX  my_index ON DOC_CONTENTS(doc_contentINDEXTYPE IS CTXSYS.CONTEXT ;  // doc_content  has blob type.

      SQL> index created

      Now all indexes are created in the tablespace DATA which is not good, they should be created in the tablespace INDX (now is empty)

       

      For this reason, and after searching, I specified the tablespace INDX  that will contain the indexes, and the command used is:

      SQL> CREATE INDEX  my_index ON DOC_CONTENTS(doc_contentINDEXTYPE IS CTXSYS.CONTEXT TABLESPACE  INDX ;

      *

      ERROR on line 1 :

      ORA-29850 : invalid option for creation of domain indexes

       

      NB: Also, when I try to use the same command with varchar column, it works.

      SQL> CREATE INDEX  my_index ON DOC_CONTENTS(doc_name TABLESPACE  INDX ;  // doc_content  has VARCHAR2 type.

      SQL> index created


      Have you any idea on how to create indexes on blob column in another tablespace ?

        • 1. Re: Creating INDEX on BLOB Column in separate tablespace
          Barbara Boehmer

          This question has nothing to do with Oracle Objects, but has to do with Oracle Text, so perhaps some moderator will move it from Objects to Text.

           

          In order to specify a tablespace for the domain index tables of an Oracle Text ctxsys.context index, you have to create a storage preference, specify the tablespaces in the attributes of that preference, then use that preference in the parameters of the index creation.  Please see the example below that first shows creation of the domain index tables in the default users tablespace, then creation of the domain index tables in the example tablespace.

           

          SCOTT@orcl_11gR2> -- test environment:

          SCOTT@orcl_11gR2> CREATE TABLE doc_contents

            2    (doc_content  BLOB)

            3  /

           

          Table created.

           

          SCOTT@orcl_11gR2> INSERT INTO doc_contents VALUES

            2    (UTL_RAW.CAST_TO_RAW ('test data'))

            3  /

           

          1 row created.

           

          SCOTT@orcl_11gR2> -- creation of domain index tables in default tablespace users:

          SCOTT@orcl_11gR2> CREATE INDEX my_index

            2  ON doc_contents (doc_content)

            3  INDEXTYPE IS CTXSYS.CONTEXT

            4  /

           

          Index created.

           

          SCOTT@orcl_11gR2> SELECT index_name, tablespace_name

            2  FROM   user_indexes

            3  WHERE  index_name LIKE '%MY_INDEX%'

            4  /

           

          INDEX_NAME                     TABLESPACE_NAME

          ------------------------------ ------------------------------

          MY_INDEX

          DR$MY_INDEX$X                  USERS

           

          2 rows selected.

           

          SCOTT@orcl_11gR2> SELECT table_name, tablespace_name

            2  FROM   user_tables

            3  WHERE  table_name LIKE '%MY_INDEX%'

            4  /

           

          TABLE_NAME                     TABLESPACE_NAME

          ------------------------------ ------------------------------

          DR$MY_INDEX$I                  USERS

          DR$MY_INDEX$R                  USERS

          DR$MY_INDEX$N

          DR$MY_INDEX$K

           

          4 rows selected.

           

          SCOTT@orcl_11gR2> -- creation of domain index tables in tablespace example:

          SCOTT@orcl_11gR2> DROP INDEX my_index

            2  /

           

          Index dropped.

           

          SCOTT@orcl_11gR2> begin

            2    ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');

            3    ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE',

            4                   'tablespace example storage (initial 1K)');

            5    ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE',

            6                   'tablespace example storage (initial 1K)');

            7    ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE',

            8                   'tablespace example storage (initial 1K) lob

            9                    (data) store as (disable storage in row cache)');

          10    ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE',

          11                   'tablespace example storage (initial 1K)');

          12    ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE',

          13                   'tablespace example storage (initial 1K) compress 2');

          14    ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE',

          15                   'tablespace example storage (initial 1K)');

          16    ctx_ddl.set_attribute('mystore', 'S_TABLE_CLAUSE',

          17                   'tablespace example storage (initial 1K)');

          18  end;

          19  /

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl_11gR2> CREATE INDEX my_index

            2  ON doc_contents (doc_content)

            3  INDEXTYPE IS CTXSYS.CONTEXT

            4  PARAMETERS ('STORAGE mystore')

            5  /

           

          Index created.

           

          SCOTT@orcl_11gR2> SELECT index_name, tablespace_name

            2  FROM   user_indexes

            3  WHERE  index_name LIKE '%MY_INDEX%'

            4  /

           

          INDEX_NAME                     TABLESPACE_NAME

          ------------------------------ ------------------------------

          MY_INDEX

          DR$MY_INDEX$X                  EXAMPLE

           

          2 rows selected.

           

          SCOTT@orcl_11gR2> SELECT table_name, tablespace_name

            2  FROM   user_tables

            3  WHERE  table_name LIKE '%MY_INDEX%'

            4  /

           

          TABLE_NAME                     TABLESPACE_NAME

          ------------------------------ ------------------------------

          DR$MY_INDEX$I                  EXAMPLE

          DR$MY_INDEX$R                  EXAMPLE

          DR$MY_INDEX$N

          DR$MY_INDEX$K

           

          4 rows selected.

           

          Message was edited by: BarbaraBoehmer (fixed error due to preference already existing)

          • 2. Re: Creating INDEX on BLOB Column in separate tablespace
            amostapha

            thank you for the answer;

             

            I reached the following result:

             

            INDEX_NAME                     TABLESPACE_NAME

            ------------------------------ ------------------------------

            MY_INDEX

            DR$MY_INDEX$X                  EXAMPLE

             

            Is my_index exist in EXAMPLE tablespace now?

             

             

            But sometimes the following error occurred when creating the procedure:


            ORA-20000: Erreur Oracle Text :

            DRG-10704: la préférence MYSTORE n'a pas d'attribut intitulé I_INDX_CLAUSE

            ORA-06512: à "CTXSYS.DRUE", ligne 160

            ORA-06512: à "CTXSYS.CTX_DDL", ligne 56

            ORA-06512: à ligne 1

             

            Any ideas?

            • 3. Re: Creating INDEX on BLOB Column in separate tablespace
              Barbara Boehmer
              DRG-10704: la préférence MYSTORE n'a pas d'attribut intitulé I_INDX_CLAUSE
              You left out the E in INDEX in I_INDEX_CLAUSE.