Home » Server Options » Text & interMedia » Oracle Text added column name to tokens (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0)
Oracle Text added column name to tokens [message #346662] Tue, 09 September 2008 05:03 Go to next message
smcdonald
Messages: 2
Registered: September 2008
Junior Member
Hi,

i have a problem with Oracle Text on our productive system.
For reproduce the problem I created a small table with three columns and checked the token_text values. Somehow Oracle Text also adds the column name to the indexed tokens. If a user now searchs for a text that doesn't exists but in the column name he finds all stored data.

Create Table and insert data
SQL> create table number_test (
  2      value_1 varchar(255),
  3      value_2 varchar(255),
  4      value_45 varchar(1)
  5  );

Table created.

SQL> insert into number_test (value_1, value_2) values ('some text', 'another text');

1 row created.

SQL> commit work;

Commit complete.


Create the preferences and index
SQL> begin
  2    ctx_ddl.create_preference('TEST_STORE', 'MULTI_COLUMN_DATASTORE');
  3    ctx_ddl.set_attribute('TEST_STORE', 'columns', 'value_1, value_2, value_45');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL>
SQL> CREATE INDEX idx_number_test ON number_test (value_1)
  2         INDEXTYPE IS CTXSYS.CONTEXT
  3         PARAMETERS('DATASTORE TEST_STORE');

Index created.


Select something
SQL> COLUMN value_1 FORMAT A20 word_wrapped
SQL> COLUMN value_2 FORMAT A20 word_wrapped
SQL> select value_1, value_2 from number_test where contains(value_1, '1%', 0) > 0;

VALUE_1              VALUE_2
-------------------- --------------------
some text            another text


As you can see I got a search result for the query '1%' but no data contains '1%'. Only the token created by the column name:

SQL> COLUMN token_text FORMAT A20 word_wrapped
SQL> select token_text from DR$IDX_NUMBER_TEST$I;

TOKEN_TEXT
--------------------
1
2
45
VALUE
VALÜ
another
some
text

8 rows selected.


I hope someone can explain me what I am doing wrong or is it normal that oracle also indexes the colum names?

Re: Oracle Text added column name to tokens [message #346758 is a reply to message #346662] Tue, 09 September 2008 11:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
Set the delimiter for your multi_column_datastore to newline as shown below.


SCOTT@orcl_11g> create table number_test (
  2  	 value_1 varchar(255),
  3  	 value_2 varchar(255),
  4  	 value_45 varchar(1)
  5  );

Table created.

SCOTT@orcl_11g> insert into number_test (value_1, value_2) values ('some text', 'another text');

1 row created.

SCOTT@orcl_11g> begin
  2    ctx_ddl.create_preference('TEST_STORE', 'MULTI_COLUMN_DATASTORE');
  3    ctx_ddl.set_attribute('TEST_STORE', 'columns', 'value_1, value_2, value_45');
  4    ctx_ddl.set_attribute('TEST_STORE', 'delimiter', 'NEWLINE');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX idx_number_test ON number_test (value_1)
  2  	    INDEXTYPE IS CTXSYS.CONTEXT
  3  	    PARAMETERS('DATASTORE TEST_STORE');

Index created.

SCOTT@orcl_11g> COLUMN token_text FORMAT A20 word_wrapped
SCOTT@orcl_11g> select token_text from DR$IDX_NUMBER_TEST$I;

TOKEN_TEXT
--------------------
ANOTHER
TEXT

SCOTT@orcl_11g> COLUMN value_1 FORMAT A20 word_wrapped
SCOTT@orcl_11g> COLUMN value_2 FORMAT A20 word_wrapped
SCOTT@orcl_11g> select value_1, value_2 from number_test where contains(value_1, '1%', 0) > 0;

no rows selected

SCOTT@orcl_11g> select value_1, value_2 from number_test where contains(value_1, 'another', 0) > 0;

VALUE_1              VALUE_2
-------------------- --------------------
some text            another text

SCOTT@orcl_11g> 

Re: Oracle Text added column name to tokens [message #346790 is a reply to message #346758] Tue, 09 September 2008 13:06 Go to previous message
smcdonald
Messages: 2
Registered: September 2008
Junior Member
Thanks for your answer. I've some days off till Monday so I can't test it now. I will report next week.
Previous Topic: How to pass the value to a contains query using a parameter
Next Topic: index error
Goto Forum:
  


Current Time: Wed Jul 30 07:33:19 CDT 2014

Total time taken to generate the page: 0.08201 seconds