Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Patrick.

Asked: August 29, 2018 - 2:10 pm UTC

Last updated: February 01, 2022 - 2:53 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

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

and Chris said...

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Note on the label argument

Roger, January 31, 2022 - 4:07 pm UTC

I would like to add a note since it was not obvious to me and I ended up here in my searches: The docs just call it a "label", but it was not apparent that the "label" argument can only be a literal number, not a useful string that you could use as a mnemonic in giant queries. AND they are separate from the column numbers (e.g. "order by 1 desc" ).

For me, using a different number than 1,2,3, helped.

e.g.

select c.*,
score( 999 ), contains ( car_name, 'red NEAR car', 999 ),
score( 888 ), contains ( car_name, 'red ACCUM car', 888 )
from cars c
where contains ( car_name, 'red ACCUM car', 777 ) > 0
order by score ( 777 ) desc;

I don't know what the limit on the label number is.
Chris Saxon
February 01, 2022 - 2:53 pm UTC

Good point.