5 Replies Latest reply: Nov 26, 2012 5:38 AM by Roger Ford-Oracle RSS

    Oracle Text, ctxsys.context problem with number column

    882532
      Hi,

      DB 11.2.0.3

      Is there any solution to create ctxsys.context index on number column ? Or I have to create other index type ?

      When I try do that (create ctxsys.context index), I get
      09:01:18  ORA-29855: wystąpił błąd przy wykonywaniu podprogramu ODCIINDEXCREATE  (An error has occurred in the performance of the subprogram)
      09:01:18  ORA-20000: Oracle Text - błąd: (- error:)
      09:01:18  DRG-10509: niepoprawna kolumna tekstowa: ITEM_TYPE (invalid text column:)
      09:01:18  ORA-06512: przy "CTXSYS.DRUE", linia 160
      09:01:18  ORA-06512: przy "CTXSYS.TEXTINDEXMETHODS", linia 366
      Btw, is there possible to search like "*LIKE %123%*" clause any of index (in contains) ?

      Edited by: Ndejo on 2012-11-21 00:53
        • 1. Re: Oracle Text, ctxsys.context problem with number column
          Roger Ford-Oracle
          No, you can't create a text index on a number field. You could perhaps add a dummy varchar column to your table and use the MULTI_COLUMN_DATASTORE:
          SQL> create table mytab (theNumber number, dummy varchar2(1));
          
          Table created.
          
          SQL> insert into mytab values ('9812376', 'X');
          
          1 row created.
          
          SQL> exec ctx_ddl.create_preference('myds', 'MULTI_COLUMN_DATASTORE')
          
          PL/SQL procedure successfully completed.
          
          SQL> exec ctx_ddl.set_attribute('myds', 'COLUMNS', 'theNumber')
          
          PL/SQL procedure successfully completed.
          
          SQL> create index myindex on mytab(dummy) indextype is ctxsys.context
            2  parameters ('datastore myds');
          
          Index created.
          
          
          SQL> select * from mytab where contains (dummy, '%123%') > 0;
          
           THENUMBER D
          ---------- -
             9812376 X
          But once again, this really isn't what a text index is designed for. It probably won't perform particularly well. You would need to use the SUBSTRING_INDEX option if there are more than a few rows, and you might have to boost WILDCARD_MAXTERMS. Also you'd need to ensure that the "dummy" column is updated if the "theNumber" column gets modified, otherwise the index won't get updated.
          • 2. Re: Oracle Text, ctxsys.context problem with number column
            882532
            Thank you, I have last question.

            For MULTI_COLUMN_DATASTORE how exactly does work index on dummy column ?

            When I have 5 columns: a_id, a_code, a_title, a_subtitle, a_info and I would like to search for a_code, a_title, a_subtitle. I need to add dummy column to make it work ?

            Can you explain me this ? I spend a lot of time to read about multicolumn and still, this is not clear for me..
            • 3. Re: Oracle Text, ctxsys.context problem with number column
              Roger Ford-Oracle
              The multi_column_datastore says that the text to be indexed should be fetched from the supplied COLUMNS list rather than from the actual column the index is created on.

              So in this case we're fetching the contents of "theNumber" column, converting it to text, and indexing it.

              You don't necessarily need a dummy column, if "a_title" is a varchar column you could create the index on that instead.

              For example:
              SQL> create table mytab( a_id number, a_code number, a_title varchar2(80), a_subtitle varchar2(80), a_info clob );
              
              Table created.
              
              SQL> insert into mytab values ( 12, 9812376, 'foo is the title',
                2  'foobar is the subtitle', 'the info column will not be indexed' );
              
              1 row created.
              
              SQL> exec ctx_ddl.drop_preference( 'myds' )
              
              PL/SQL procedure successfully completed.
              
              SQL> exec ctx_ddl.create_preference( 'myds', 'MULTI_COLUMN_DATASTORE' )
              
              PL/SQL procedure successfully completed.
              
              SQL> exec ctx_ddl.set_attribute( 'myds', 'COLUMNS', 'a_code, a_title, a_subtitle' )
              
              PL/SQL procedure successfully completed.
              
              SQL> create index myindex on mytab (a_title) indextype is ctxsys.context
                2  parameters ('datastore myds');
              
              Index created.
              
              SQL> select a_id from mytab where contains (a_title, '%123%') > 0;
              
                    A_ID
              ----------
                      12
              
              SQL> select a_id from mytab where contains (a_title, 'foo') > 0;
              
                    A_ID
              ----------
                      12
              If you want to be able to search within a particular column, then add "section group CTXSYS.AUTO_SECTION_GROUP" to the parameters clause, and you can then do:
              SQL> select a_id from mytab where contains (a_title, 'foobar WITHIN a_subtitle') > 0;
              
                    A_ID
              ----------
                      12
              • 4. Re: Oracle Text, ctxsys.context problem with number column
                882532
                Thank you for help.

                The solution could be to create a view and convert orignal number column to varchar2.
                • 5. Re: Oracle Text, ctxsys.context problem with number column
                  Roger Ford-Oracle
                  Would have to be a materialized view, otherwise you can't create an index on it.