SCORE

Use the SCORE operator in SELECT statements to return the score values produced by CONTAINS and JSON_TEXTCONTAINS queries.

The SCORE operator can be used in a SELECT, ORDER BY, or GROUP BY clause.

The SCORE operator also supports database links. You can identify a remote table or materialized view by appending @dblink to the end of its name. The dblink must be a complete or partial name for a database link to the database containing the remote table or materialized view. (Querying of remote views is not supported.)

Syntax

SCORE(label NUMBER)

Here, label specifies a number to identify the score produced by the query. Use this number to identify the CONTAINS clause that returns this score.

Notes

For nested queries, you must specify an alias to avoid errors. For example, here an alias “s” is used in the inner SELECT query to identify the outer SELECT query:

SELECT s FROM (
      SELECT SCORE(1) AS s FROM mytable
      WHERE CONTAINS(text, 'oracle', 1) > 0
);

Examples

With a single CONTAINS clause:

When the SCORE operator is called (for example, in a SELECT clause), the CONTAINS clause must reference the score label value as in the following example:

SELECT SCORE(1), title from newsindex
      WHERE CONTAINS(text, 'oracle', 1) > 0

      ORDER BY SCORE(1) DESC;

With multiple CONTAINS clauses:

Assume that a news database stores and indexes the title and body of news articles separately. The following query returns all the documents that include the words Oracle in their title and java in their body. The articles are sorted by the scores for the first CONTAINS (Oracle) and then by the scores for the second CONTAINS (java).

SELECT title, body, SCORE(10), SCORE(20)
      FROM news
      WHERE CONTAINS (news.title, 'Oracle', 10) > 0 OR

      CONTAINS (news.body, 'java', 20) > 0

      ORDER BY SCORE(10), SCORE(20);

With a single JSON_TEXTCONTAINS clause:

This query selects the PO numbers of purchase orders whose descriptions contain the text run. It orders the results by relevance using an optional scoring-label argument. The query returns also the relevance score for each purchase order.

The scoring label passed to json_textcontains must be the same as the label used with SCORE. In this case the label is 1.
```
SELECT po.po_document.PONumber, SCORE(1)
      FROM j_purchaseorder po
      WHERE json_textcontains (po.po_document,
            '$.LineItems.Part.Description',
            'run',
            1)
      ORDER BY SCORE(1) DESC;
```
Results (some elided):

The first 17 purchase orders listed have score 18; the remaining 85 purchase orders have score 9. The former group match pattern run better than the latter (they match it twice per purchase order instead of once):
```
PONUMBER SCORE(1)

-------- --------
1 18
9958 18
...
1388 18
36 9
22 9
...
8637 9
102 rows selected.
```

Related Topics