SDATA

Use the SDATA operator to perform tests on SDATA sections and columns, which contain structured data values.

SDATA sections speed up mixed querying and ordering. This operator provides structured predicate support for CONTAINS, which extends non-SQL interfaces such as count_hits or the result set interface.

SDATA operators should only be used as descendants of AND operators that also have non-SDATA children.

SDATA queries perform on string or numeric literals, and on date strings. The string literal and date string are enclosed within single or double quote characters. The numeric value is not enclosed in quote characters, and must conform to the SQL format of NUMBER. For example:

CONTAINS(text, "dog and SDATA(category = ''news'')")>0 ...

SDATA(rating between
1.2 and 3.4) ...

SDATA(author LIKE 'FFORDE%') ...

SDATA(date >='2005-09-18') ...

Closed parentheses are permitted, as long as they are enclosed in single or double quotes.

The SDATA operator can be used in query templates.

Syntax

Syntax Operators
SData := “SDATA” “(“ SDataPredicate “)”
SDataPredicate := section_name SDataTest
SDataTest := | SDataBetweenOp | <"is" ("not")? "null">
SDataSingleOp := (“<” | “<=” | “=” | “>=” | “>” | “!=” | “<>” | “like”) SDataLiteral
SDataBetweenOp := “between” SDataLiteral “and” SDataLiteral
SDataLiteral := numeric_literal | “’” string_literal “’” | “’” date_string “’”

section_name

The name of the SDATA section(s) on which to search and perform the test, or check.

SDataLiteral

The value of the SDATA section. This must be either a string literal, numeric literal, or a date string.

The SDATA operator returns a score of 100 if the enclosed predicate returns TRUE, and returns 0 otherwise. In the case of a NULL value, the SDATA operator returns a score of 0 (since in SQL it would not return TRUE).

Multi-valued semantics are not defined, as multi-valued SDATA sections are not supported.

Comparison of strings is case sensitive. The BINARY collation is always used.

Note: For the SDATA operator on SDATA sections that are mapped to a DATE FILTER BY column, the SDATA value must follow the Date format: YYYY-MM-DD or YYYY-MM-DD HH24:MI:SS. Otherwise, the expected rows will not be returned. If the time component is omitted, it will default to 00:00:00, according to SQL semantics. This Date format is always used, regardless of the setting of the NLS_DATE_FORMAT environment variable.

Example for SDATA Operator

Suppose that you want to query for books in the fiction category that contain the word summer. Assuming that an SDATA section called CATEGORY has been declared, you can query as follows:

SELECT id FROM idx_docs
  WHERE CONTAINS(text, 'summer AND SDATA(category = "fiction")')>0

Restrictions

Notes

Stoplists do not affect string-value SDATA sections, that is, if a stopword is present within an SDATA section, then the token will still be indexed and can be queried using the SDATA operator.

Oracle recommends using SDATA operators only as descendants of AND operators that also have non-SDATA children. Essentially, use SDATA operators as secondary (that is, checking or non-driving) criteria. For instance, “find documents with DOG that also have price > 5”, rather than “find documents with rating > 4”. Other usage may operate properly, but may not have optimal performance.

The following examples are consistent with recommended use:

dog & SDATA(foo = 5)

The SDATA is a child of an AND operator that also has non-SDATA children.

dog & (SDATA(foo = 5) | SDATA(x = 1))

Although the SDATA operators here are children of OR, they are still descendants of an AND operator with non-SDATA children.

The following examples show use that is not recommended:

SDATA(foo = 5)

Here, SDATA is the only criteria and, therefore, the driving criteria.

dog | SDATA(bar = 9)

The SDATA in this example is a child of an OR operator rather than an AND.

SDATA(foo = 5) & SDATA(bar = 7)

While both SDATA operators in this example are descendants of AND, this AND operator does not have non-SDATA children.

Related Topics