| Oracle Text added column name to tokens [message #346662] |
Tue, 09 September 2008 05:03  |
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  |
 |
Barbara Boehmer
Messages: 9104
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>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
