Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536.1K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.6K Security Software
How to use a Multi_Column_Datastore index to search, but excluding a certain column?
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...
Best 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;
Answers
-
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; -
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