Re: MULTI_COLUMN_DATASTORE issue

From: Thomas Hesse <thomas.hesse_at_team.xtra.co.nz>
Date: Wed, 30 Jul 2003 08:08:48 +1200
Message-ID: <3F26D450.9030108@team.xtra.co.nz>

Hi Venkat,

You are right, searching for 'text' (column name) should not result in retrieving all rows. Have a look in your token table if there are tokens   called 'text' (select token_text from dr$ctx_test1_idx1$i). It is maybe a bug. Which version are you using ?

You can also try building a MULTI_COLUMN_DATASTORE with a BASIC_SECTION_GROUP (see
"http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/addendum.817/a85455/imt_adde.htm#78621")

You have to define a field section for each column e.g.:

as ctxsys:
exec ctx_ddl.Create_Preference ('MY_MULTICOLUMN','MULTI_COLUMN_DATASTORE'); exec CTX_DDL.set_attribute('MY_MULTICOLUMN', 'COLUMNS', 'country,text');

as user:
exec ctx_ddl.drop_section_group ('MY_SECTION'); begin

   ctx_ddl.create_section_group

     (
        group_name => 'MY_SECTION',
        group_type => 'basic_section_group'
     );
   ctx_ddl.add_field_section
     (
       group_name   => 'MY_SECTION',
       section_name => 'country',
       tag          => 'country',
       visible      => false
     );
   ctx_ddl.add_field_section
     (
       group_name   => 'MY_SECTION',
       section_name => 'text',
       tag          => 'text'
     );

end;
/

create index i_test_multicolumn on test_multicolumn (text)

   indextype is ctxsys.context
   parameters ('lexer MY_LEXER filter MY_FILTER section group MY_SECTION datastore ctxsys.MY_MULTICOLUMN');

Hope that helps
Thomas

Venkat Tipparam wrote:
> I am using Oracle Text's multi_column_datastore to create a compound
> text index on group of columns. It seems to work fine however when I
> search for one of the column names in the index it matches all rows.
> Please see the script below.
>
> The following example uses multi_column_datastore but creates index on
> just one column for the demo purpose.
>
> you will have to grant execute permission on ctx_ddl to user scott to
> run the script.
>
> connect ctxsys/ctxsys;
>
> begin
> ctx_ddl.drop_preference('TEST_MULTI_PREF');
> end;
> /
>
> begin
> ctx_ddl.create_preference('TEST_MULTI_PREF',
> 'MULTI_COLUMN_DATASTORE');
> ctx_ddl.set_attribute('TEST_MULTI_PREF', 'COLUMNS', 'TEXT');
> end;
> /
>
> connect scott/tiger;
>
> drop table ctx_test1;
>
> create table ctx_test1 (
> id number primary key,
> text varchar2(4000),
> dummy char(1)
> );
>
> create index ctx_test1_idx1 on ctx_test1(dummy) indextype is
> ctxsys.context parameters ('datastore CTXSYS.TEST_MULTI_PREF');
>
> insert into ctx_test1(id,text) values (100, 'first line');
> insert into ctx_test1(id,text) values (101, 'second line');
> insert into ctx_test1(id,text) values (102, 'third line');
> insert into ctx_test1(id,text) values (103, 'fourth line');
> insert into ctx_test1(id,text) values (104, 'fifth line');
>
> commit;
>
> begin
> ctx_ddl.sync_index('CTX_TEST1_IDX1');
> end;
> /
>
> select text from ctx_test1 where contains(dummy, 'text') > 0;
>
>
>
> Thanks in advance,
>
> Venkat Tipparam
Received on Tue Jul 29 2003 - 15:08:48 CDT