3 Replies Latest reply: Aug 27, 2012 10:07 AM by Roger Ford-Oracle RSS

    ORACLE Text Search -DEFINESCORE with COMPLETION

    Ganesh Jadhav
      Hi,

      I have created MULTI_COLUMN_DATASTORE indexes on certain columns of tables.

      Now i have different combinations to search with different scoring method(RELEVANCE,COMPLETION)

      My query text looks like...

      Code: [Select all] [Show/ hide]

      ...
      '<query>
      <textquery grammar="CONTEXT" lang="english">
      DEFINESCORE (Labradors are big dog, RELEVANCE) * 10
      ,DEFINESCORE (dog, COMPLETION) WITHIN zonesection * 5
      </textquery>
      <score datatype="integer" algorithm="COUNT"/>.
      </query>'
      ...

      With this i am trying to get all related results and most matching one on top.

      Now my question is..
      For WITHIN section, how can i define zonesection for table or columns?

      Regards

      Ganesh Jadhav
        • 1. Re: ORACLE Text Search -DEFINESCORE with COMPLETION
          Roger Ford-Oracle
          I don't really understand the question. Are you asking how to define a section for one of the columns used in the multi_column_datastore?

          If so, you can either use AUTO_SECTION_GROUP which will automatically create zone sections for each column, or you can manually define them as part of a BASIC_SECTION_GROUP:
          SQL> create table foo (colfoo varchar2(99), colbar varchar2(99));
          
          Table created.
          
          SQL> exec ctx_ddl.create_preference('mcds', 'MULTI_COLUMN_DATASTORE')
          
          PL/SQL procedure successfully completed.
          
          SQL> exec ctx_ddl.set_attribute('mcds', 'COLUMNS', 'colfoo, colbar')
          
          PL/SQL procedure successfully completed.
          
          SQL> exec ctx_ddl.create_section_group('mcsg', 'BASIC_SECTION_GROUP')
          
          PL/SQL procedure successfully completed.
          
          SQL> exec ctx_ddl.add_zone_section('mcsg', 'colfoo', 'colfoo')
          
          PL/SQL procedure successfully completed.
          
          SQL> exec ctx_ddl.add_zone_section('mcsg', 'colbar', 'colbar')
          
          PL/SQL procedure successfully completed.
          
          SQL> create index fooindex on foo(colfoo) indextype is ctxsys.context
            2  parameters ('datastore mcds section group mcsg');
          
          Index created.
          • 2. Re: ORACLE Text Search -DEFINESCORE with COMPLETION
            Ganesh Jadhav
            Thanks Roger

            It is really helpful...

            As you created zone section mcsg on two columns...

            Can i query WITHIN colfoo and colbar in one query?

            Regards

            Ganesh Jadhav
            • 3. Re: ORACLE Text Search -DEFINESCORE with COMPLETION
              Roger Ford-Oracle
              Yes, if you don't use a WITHIN clause then it will automatically search across both columns.

              Or you do things like

              select * from foo where contains (colfoo, ' (dog AND cat ) WITHIN foocol OR (cat AND rabbit) WITHIN barcol') >0