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?
] 
Blog
Search
Help
Members
Register
Login
Home
