Forum Stats

  • 3,813,998 Users
  • 2,258,806 Discussions
  • 7,892,481 Comments

Discussions

How to use a Multi_Column_Datastore index to search, but excluding a certain column?

DannyS-Oracle
DannyS-Oracle Member Posts: 165
edited Jan 29, 2018 7:49AM in Text

Hi everyone, I am a beginner in Oracle Text, please be kind to me .

I have built a multi_column_datastore CONTEXT index on my application. And then, I have a specific case where I want to perform a search using this index, but excluding one of the columns.

For example, the index was built on columnA, columnB, and columnC. I want to check whether my database entries contain a keyword, which exist on any columns other than column C, using the same index. Is it possible to do this? Or do I have to build another index just to perform this check?

Thank you in advance...

Tagged:
DannyS-Oracle

Best Answer

  • Bud Light
    Bud Light Member Posts: 70 Blue Ribbon
    edited Jan 26, 2018 12:46PM Answer ✓

    There may be another way to do this but off the top of my head I would look into Section Groups.  Then you can use WITHIN.

    Here is a simple test case that shows what I think you are looking for.

    drop table tab1 purge;
    create table tab1(idx_col char(1), columnA varchar2(10),columnB varchar2(10),columnC varchar2(10));insert into tab1 values(null,'Hello','World','Green');
    insert into tab1 values(null,'Goodbye','World','Red');
    commit;
    exec ctx_ddl.drop_preference('my_multi');
    begin
    ctx_ddl.create_preference('my_multi', 'MULTI_COLUMN_DATASTORE');
    ctx_ddl.set_attribute('my_multi', 'columns', 'columnA, columnB, columnC');
    end;
    /exec ctx_ddl.drop_section_group('my_basicgroup');begin
    ctx_ddl.create_section_group('my_basicgroup', 'BASIC_SECTION_GROUP');
    ctx_ddl.add_field_section('my_basicgroup', 'notC', 'COLUMNA', TRUE);
    ctx_ddl.add_field_section('my_basicgroup', 'notC', 'COLUMNB', TRUE);
    end;
    /create index tab1_idx on tab1(idx_col) indextype is ctxsys.context
    parameters('datastore my_multi section group my_basicgroup');--find Green anywhere:
    select * from tab1 where contains(idx_col,'Green') >0;--Look for Red NOT in ColumnC:
    select * from tab1 where contains(idx_col,'Red within notC') >0;--Show you do still search ColumnA within notC:
    select * from tab1 where contains(idx_col,'Goodbye within notC') >0;
    DannyS-Oracle

Answers

  • Bud Light
    Bud Light Member Posts: 70 Blue Ribbon
    edited Jan 26, 2018 12:46PM Answer ✓

    There may be another way to do this but off the top of my head I would look into Section Groups.  Then you can use WITHIN.

    Here is a simple test case that shows what I think you are looking for.

    drop table tab1 purge;
    create table tab1(idx_col char(1), columnA varchar2(10),columnB varchar2(10),columnC varchar2(10));insert into tab1 values(null,'Hello','World','Green');
    insert into tab1 values(null,'Goodbye','World','Red');
    commit;
    exec ctx_ddl.drop_preference('my_multi');
    begin
    ctx_ddl.create_preference('my_multi', 'MULTI_COLUMN_DATASTORE');
    ctx_ddl.set_attribute('my_multi', 'columns', 'columnA, columnB, columnC');
    end;
    /exec ctx_ddl.drop_section_group('my_basicgroup');begin
    ctx_ddl.create_section_group('my_basicgroup', 'BASIC_SECTION_GROUP');
    ctx_ddl.add_field_section('my_basicgroup', 'notC', 'COLUMNA', TRUE);
    ctx_ddl.add_field_section('my_basicgroup', 'notC', 'COLUMNB', TRUE);
    end;
    /create index tab1_idx on tab1(idx_col) indextype is ctxsys.context
    parameters('datastore my_multi section group my_basicgroup');--find Green anywhere:
    select * from tab1 where contains(idx_col,'Green') >0;--Look for Red NOT in ColumnC:
    select * from tab1 where contains(idx_col,'Red within notC') >0;--Show you do still search ColumnA within notC:
    select * from tab1 where contains(idx_col,'Goodbye within notC') >0;
    DannyS-Oracle
  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Jan 29, 2018 7:49AM

    Bud is correct - sections are the way to do it.  The fourth argument to create_field_section (TRUE in this example) is the 'visible' flag. That simply indicates that the text in the field should also be indexed as 'ordinary' text that can be found without using a WITHIN clause.

    A limitation of this technique is that you can't define a further section NotB which contains COLUMNA and COLUMNC.  You'll get an error:

    ORA-20000: Oracle Text error:

    DRG-12206: tag COLUMNA already exists in section group MY_BASICGROUP

    If you need to be able to exclude any column then the best bet is to create individual sections for each column and then use something like:

    where contains (idx_col, '(red) within ColumnA OR (red) within ColumnB') > 0

    DannyS-OracleDannyS-Oracle
This discussion has been closed.