Hey AskTom,
the documentation for Oracle Text is very poor so I have a question: Is it best to use the CONTAINS() clause to find a certain result, or should we use score(1) for this operator?
I mean such a case where we are interested in score = 50:
Select CAR, SCORE(1) from Car_Table where contains() = 50
whether
Select CAR, SCORE(1) from Car_Table where contains() > 0 and SCORE(1) = 50
You put your search criteria in contains. Score ranks how well the row matches these criteria.
So you use contains to find certain rows. Then score to sort the output. Score is shorthand for your contains predicate. This saves you duplicating (possibly complicated) criteria.
The third parameter of contains is a label. Use this label in score to get the result of that contains.
For example, the ACCUM operator looks for entries that contain at least one of the terms listed. The more terms it matches, the higher it scores.
NEAR scores the text based on how close the search terms are within the text. The fewer other characters between them, the higher the score.
The following searches for rows that include either "red" or "car", with rows including both ranking higher. It also shows the score and contains for a NEAR search on red and car.
The third score and contains are the same as the first. I merely selected these to show these values:
create table cars (
car_name varchar2(20) not null
);
insert into cars values ('Fast red car');
insert into cars values ('Slow car');
insert into cars values ('Red stripy car');
insert into cars values ('Red van');
commit;
create index text_i on cars ( car_name ) indextype is ctxsys.context;
select c.*,
score( 2 ), contains ( car_name, 'red NEAR car', 2 ),
score( 3 ), contains ( car_name, 'red ACCUM car', 3 )
from cars c
where contains ( car_name, 'red ACCUM car', 1 ) > 0
order by score ( 1 ) desc;
CAR_NAME SCORE(2) CONTAINS(CAR_NAME,'REDNEARCAR',2) SCORE(3) CONTAINS(CAR_NAME,'REDACCUMCAR',3)
Red stripy car 14 14 52 52
Fast red car 14 14 52 52
Red van 0 0 2 2
Slow car 0 0 2 2
There are many more examples in the docs:
https://docs.oracle.com/en/database/oracle/oracle-database/18/ccapp/querying-with-oracle-text.html#GUID-13F9B749-125B-40FD-9AFD-A636597447D0 https://docs.oracle.com/en/database/oracle/oracle-database/18/ccref/oracle-text-CONTAINS-query-operators.html#GUID-6410B783-FC9A-4C99-B3AF-9E0349AA43D1