ADD_SDATA_SECTION

This procedure adds an SDATA section to a section group. By default, all FILTER BY columns are mapped as SDATA.

Starting with Oracle Database 12c Release 2 ( 12.2), searchable multi-valued SDATA sections are supported. There is no restriction on the number of SDATA sections that can be created for an index. That is, the sum total of SDATA sections for an index, created implicitly with FILTER BY and ORDER BY, and explicitly with the CTX_DDL.ADD_SDATA_SECTION() procedure is not restricted anymore. The total number of CDI, including FILTER BY and ORDER BY is 32, but the number of SDATA sections supported is unlimited. There are two types of SDATA sections:

Starting with Oracle Database Release 18c, group counts or facets are supported for SDATA sections that are created with the optimized_for attribute set to sort, search, or sort_and_search. The optimized_for attribute can be set by using the CTX_DDL.SET_SECTION_ATTRIBUTE procedure.

See Also:

Syntax

The syntax is as follows:

CTX_DDL.ADD_SDATA_SECTION(
     group_name         IN VARCHAR2,
     section_name       IN VARCHAR2,
     tag                IN VARCHAR2,
     datatype           IN VARCHAR2 default NULL,
);

group_name

Name of the group that contains the section.

section_name

Name of the SDATA section.

tag

Name of the tag to add to the SDATA section.

datatype

Specifies the stored format for the data, as well as the semantics of comparison in later use in SDATA operators. The Sortable SDATA sections support the following data types:

The VARCHAR2 datatype stores up to 249 bytes of character data in the database character set. Values larger than this result in a per-document indexing error. Note that leading and trailing whitespace are always trimmed from SDATA section values when extracted by the sectioner. This is different than SDATA columns. Column values are never trimmed. No lexing is performed on the value from either kind of SDATA.

The CHAR datatype stores up to 249 bytes of character data in the database character set. Values larger than this result in a per-document indexing error. Note that leading and trailing whitespace are always trimmed from SDATA section values when extracted by the sectioner. This is different than SDATA columns. Column values are never trimmed. No lexing is performed on the value from either kind of SDATA. To be consistent with SQL, the comparisons of CHAR datatype SDATA values are blank-padded comparisons.

The RAW datatype stores up to 249 bytes of binary data. Values larger than this result in a per-document indexing error. The value is converted from hexadecimal string representation. That is, to store a value of 65, the document appears as <TAG>40</TAG>, and not <TAG>65</TAG> or <TAG>A</TAG>.

The DATE datatype values must conform to the following format: YYYY-MM-DD or YYYY-MM-DD HH24:MI:SS. That is, to store a DATE value of “Nov. 24, 2006 10:32 pm 36 sec”, the document appears as <TAG>2006-11-24 22:32:36</TAG>.

The Searchable SDATA sections support the following data types:

Note: The Searchable SDATA sections do not support CHAR datatype.

The BINARY_FLOAT datatype stores 32-bit floating point number.

The BINARY_DOUBLE datatype stores 64-bit floating point number.

The TIMESTAMP datatype is an extension of the DATE datatype. It stores year, month, and day values of date, as well as hour, minute, and second values of time. It also stores fractional seconds, which are not stored by the DATE datatype. The fractional seconds precision cannot be more than 9. The TIMESTAMP values must follow the ISO format. You can specify the TIMESTAMP literal in the YYYY-MM-DDTHH:MI:SS format. An example of the TIMESTAMP value is:

<TAG>1997-11-05T19:20:00</TAG>

The TIMESTAMP_WITH_TIMEZONE datatype is a variant of TIMESTAMP datatype that includes a time zone offset or a time zone region name in its value. The fractional seconds precision cannot be more than 9. The TIMESTAMP_WITH_TIMEZONE values must follow the ISO format. An example of the TIMESTAMP_WITH_TIMEZONE value is:

<TAG>1997-12-31T19:20:00-05:00</TAG>

Example

The following example demonstrates how to create a SDATA section:

create table tab(id number, info varchar2(100));

insert into tab values(1,'Hello World<fruit>apple</fruit><price>3</price>');
insert into tab values(2,'Hello World<fruit>orange</fruit><price>5</price>');

The preceding statements create a table named tab with two rows of data.

The following statements create a basic section group named sg, add SDATA sections to it and mark the SDATA to be searchable:

exec ctx_ddl.create_section_group('sg', 'basic_section_group');
exec ctx_ddl.add_sdata_section('sg','fruit','fruit','varchar2');
exec ctx_ddl.set_section_attribute('sg','fruit','optimized_for','search');
exec ctx_ddl.add_sdata_section('sg','price','price','number');
exec ctx_ddl.set_section_attribute('sg','price','optimized_for','search');

The following statement creates an index on sg:

create index idx on tab(info) indextype is ctxsys.context parameters ('section group sg');

The following statements query tab to demonstrate searchable SDATA:

Query 1

select id from tab where CONTAINS(info, 'SDATA(fruit = "apple")'); return id 1

Query 2

select id from tab where CONTAINS(info, 'Hello and SDATA(price > 4)'); return id 2

Limitations

See Also: