I'm getting a weird behaviour using Oracle Text. I have created the following table:
CREATE TABLE TEST_OTEXT_TABLE (id NUMBER PRIMARY KEY, text VARCHAR2(200));
and populated the table with the following data:
1 California is a state in the US.
2 Paris is a city in France.
3 France is in Europe.
4 Italy is a city in Europe.
I've then created the index:
CREATE INDEX TEST_OTEXT_INDEX ON TEST_OTEXT_TABLE(text)
INDEXTYPE IS CTXSYS.CONTEXT
parameters ('DATASTORE CTXSYS.DEFAULT_DATASTORE');
EXEC CTX_DDL.SYNC_INDEX('TEST_OTEXT_INDEX', '2M');
If I run
select id, score(1) from TEST_OTEXT_TABLE WHERE CONTAINS (text, 'is a city', 1) > 0 ;
everything works fine as Oracle Text correctly returns
ID SCORE(1)
---------- ----------
2 4
4 4
However, if I run
select id, score(1) from TEST_OTEXT_TABLE WHERE CONTAINS (text, 'is a', 1) > 0 ;
it returns
no rows selected
instead of returning the very same result.
It is because score is too low:
select id, score(1) from TEST_OTEXT_TABLE WHERE CONTAINS (text, 'is a', 1) = 0 ;
Row ID SCORE
1 1 0
2 2 0
3 3 0
4 4 0
If you use city the score is 4 and results are shown:
select id, score(1) from TEST_OTEXT_TABLE WHERE CONTAINS (text, 'city', 1) > 0 ;
More info about score: https://docs.oracle.com/cd/A91202_01/901_doc/text.901/a90121/csql8.htm
Here you can read how score algoritm works: https://docs.oracle.com/cd/A91202_01/901_doc/text.901/a90121/ascore2.htm