3 Replies Latest reply on Jan 17, 2013 9:42 AM by Roger Ford-Oracle

    searching case sensitive and non case sensitive

    jymarkusg
      Hi!

      We're using 11g enterprise edition for our product. In the past we used the LIKE operator to search our string fields. Therefore we had a function based index (UPPER(stringfield)) for non case sensitive and a second normal index for case sensitive search.

      Now we want to use oracle text option for searching (better performance, search similar words,...). How does oracle text work with case sensitive and none case sensitive?

      As far as I know I can define a lexer and set a property for case OR non case sensitive... But I need both... Do I need 2 text indizes?!?!?!

      Thanks
      Markus
        • 1. Re: searching case sensitive and non case sensitive
          Roger Ford-Oracle
          You really need two indexes. Although if your strings are relatively short you could create a case-sensitive index, but then copy the data into another section, forcing the case to upper or lower. We could do that with a MULTI_COLUMN_DATASTORE:
          drop table my_table
          /
          create table my_table( text varchar2(2000) )
          /
          
          insert into my_table values( 'the quick brown fox jumps over the lazy dog' );
          insert into my_table values( 'Mr. Brown jumps over the lazy dog' );
          insert into my_table values( 'BROWN is a colour' );
          
          
          exec ctx_ddl.drop_preference      ( 'mylex' )
          exec ctx_ddl.create_preference    ( 'mylex', 'BASIC_LEXER' )
          exec ctx_ddl.set_attribute        ( 'mylex', 'MIXED_CASE', 'yes' )
          
          exec ctx_ddl.drop_preference      ( 'myds' )
          exec ctx_ddl.create_preference    ( 'myds', 'MULTI_COLUMN_DATASTORE' )
          exec ctx_ddl.set_attribute        ( 'myds', 'COLUMNS', 'text, lower(text) as case_insens' )
          
          exec ctx_ddl.drop_section_group   ( 'mysg' )
          exec ctx_ddl.create_section_group ( 'mysg', 'BASIC_SECTION_GROUP' )
          exec ctx_ddl.add_field_section    ( 'mysg', 'case_insens', 'case_insens', false )
          
          create index my_index on my_table( text) indextype is ctxsys.context
          parameters ( 'lexer mylex datastore myds section group mysg' )
          /
          Then we can run queries against either the main text, which will be case-sensitive, or against the lower-cased version of the text. Note that we MUST force our query terms to lower case for the "case-insensitive" search to work.
          SQL> select * from my_table where contains( text, 'Brown' ) > 0;
          
          TEXT
          --------------------------------------------------------------------------------
          Mr. Brown jumsp over the lazy dog
          
          SQL> select * from my_table where contains( text, 'brown within case_insens' ) > 0;
          
          TEXT
          --------------------------------------------------------------------------------
          the quick brown fox jumps over the lazy dog
          Mr. Brown jumps over the lazy dog
          BROWN is a colour
          Edited by: Roger Ford on Jan 16, 2013 4:32 AM
          1 person found this helpful
          • 2. Re: searching case sensitive and non case sensitive
            jymarkusg
            Thanks for your input - my strings are relatively short (about 200 characters) so MULTI_COLUMNS_DATASTORE is an option for me.

            Just for my understanding allow me one more question. If I would have two more tables:
            create table my_table2( text varchar2(100) );
            create table my_table3( col_value varchar2(4000) );

            For table my_table2 I could use the same preferences for the text index as used in my_table:

            create index my_index on my_table2(text) indextype is ctxsys.context
            parameters ( 'lexer mylex datastore myds section group mysg' );

            But in my_table3 the column name for the text index is different. Therefore I need a second multi_column_datastore:
            exec ctx_ddl.drop_preference ( 'myds2' );
            exec ctx_ddl.create_preference ( 'myds2', 'MULTI_COLUMN_DATASTORE' );
            exec ctx_ddl.set_attribute ( 'myds2', 'COLUMNS', 'col_value, lower(col_value) as case_insens' );

            In the index I have to use myds2 instead of myds - the lexer and the section group can be reused. Am I right???

            create index my_index3 on my_table3( col_value) indextype is ctxsys.context
            parameters ( 'lexer mylex datastore myds2 section group mysg' );

            Thanks
            Markus