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:

Searchable: Creates optimized for search SDATA sections which support multiple values per document for the section and efficient range search capability.
Sortable: Creates optimized for sort SDATA sections which support a single value per document for the section. If the optimized_for attribute is not set, then the default type of section is Sortable. The Composite Domain Index uses Sortable SDATA internally for efficient FILTER BY or ORDER BY evaluation.

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.

Syntax

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 supported datatypes for searchable SDATA sections are:

VARCHAR2: 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.
NUMBER: Stores numeric literals.
DATE: The DATE datatype values must conform to the YYYY-MM-DD or YYYY-MM-DD HH24:MI:SS format. For example, 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>
BINARY_FLOAT: Stores 32-bit floating point number.
BINARY_DOUBLE: Stores 64-bit floating point number.
TIMESTAMP: 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. For example:

<TAG>1997-11-05T19:20:00</TAG>
TIMESTAMP_WITH_TIMEZONE: 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. For example:

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

Note: The Searchable SDATA sections do not support the CHAR and RAW datatypes.

The supported datatypes for sortable SDATA sections are:

VARCHAR2: 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.
CHAR: 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.
RAW: 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>.
NUMBER: Stores numeric literals.
DATE: The DATE datatype values must conform to the YYYY-MM-DD or YYYY-MM-DD HH24:MI:SS format. For example, 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>

Note: The Sortable SDATA sections support the TIMESTAMP datatype when specified using the sdata name in hitlist. In this case, you must explicitly specify the TIMESTAMP datatype using the optimized_for attribute for search or sort_and_search attribute values. A detailed example on this is given at the end of this section.

Limitations

If no SDATA tag occurs in a given document, then this is treated as an SDATA value of NULL.
Empty SDATA tags are treated as NULL values.
SDATA sections cannot be nested. Sections that are nested inside are ignored.
SDATA sections do not support skipjoins and printjoins characters.

Examples

To create and query a Searchable SDATA section:

The following statements create a table named tab with two rows of data:

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 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

To create and query a Sortable SDATA section:

Here, you can see how to use the timestamp data type with sdata name in hitlist for Sortable SDATA sections.

The following statements create a table named t1:

create table t1(text varchar2(100));
insert into t1 values('Oracle1 <TAG1>1997-01-31T09:26:50.12</TAG1>');

The following statements create a basic section group named sg, add an SDATA section (sec01) to it, and mark the SDATA section to be sortable with the timestamp data type and the optimized for attribute:

exec ctx_ddl.create_section_group('sg','basic_section_group');
exec ctx_ddl.add_sdata_section('sg','sec01','tag1','timestamp');
exec ctx_ddl.set_section_attribute('sg','sec01','optimized_for','sort');

The following statement creates an index idx1 on sg:

create index idx1 on t1(text)
     indextype is ctxsys.context parameters('section group sg');

The following statement queries t1 to demonstrate sortable SDATA. Here, you specify the SDATA section (sec01) in hitlist using sdata name:

select * from ctx_user_index_errors;

set long 32000
set pagesize 0
variable displayrs clob;

declare
 rs clob;
begin
 ctx_query.result_set('idx1','Oracle1',
'<ctx_result_set_descriptor>
 <count/>
 <hitlist start_hit_num="1" end_hit_num="10">
 <score/>
 <sdata name="sec01"/>
 </hitlist>
 </ctx_result_set_descriptor>',rs);
 select rs into :displayrs from dual;
 dbms_lob.freetemporary(rs);
end;
/
select :displayrs from dual;

The output returns a document that matches the keyword Oracle1, with the SDATA attribute for the document as w?3 and the relevance score of 3:

<ctx_result_set><hitlist><hit><score>3</score>
<sdata name="SEC01">w?3'</sdata></hit></hitlist>
<count>1</count></ctx_result_set>

Related Topics