Re: MULTI_COLUMN_DATASTORE issue

Hi Thomas,

Thanks for your help. The token table does have the word 'text' in it. I am using Oracle 9.2.0.1.0. I tried creating mult_column_datastore with basic_section_group as you suggested. This time it doesn't add the column name to the token table but none of the text queries work anymore.

Thanks,

--
Venkat.

Thomas Hesse <thomas.hesse_at_team.xtra.co.nz> wrote in message news:<3F26D450.9030108_at_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