In Oracle 23c the CREATE SEARCH INDEX statement allows us to create search indexes on XML, JSON and text data, making the syntax consistent between them.
◉ XML Search Indexes
The index type of XDB.XMLIndex was introduced in a previous release to allow us to index XML data.
drop table if exists t1 purge;
create table t1 (
id number,
data xmltype
);
create index t1_xmlindex_idx on t1 (data) indextype is xdb.xmlindex;
In Oracle 23c we can create search indexes for XML using the CREATE SEARCH INDEX ... FOR XML syntax. The full syntax is available here.
drop table if exists xml_tab purge;
create table xml_tab (
id number,
data xmltype
)
xmltype column data
store as transportable binary xml;
create search index xml_tab_search_idx on xml_tab (data) for XML
parameters (
'search_on text'
);
The SEARCH_ON parameter must be set for XML search indexes.
◉ TEXT : Only text data is indexed for full-text search queries.
◉ VALUE(data_types) : Enables range-search for the specified data types. One or more of BINARY_DOUBLE, NUMBER, TIMESTAMP, VARCHAR2 as a comma-separated list.
◉ TEXT_VALUE(data_types) : A combination of support for full-text searches queries and range-search for the specified data types.
The XMLTYPE column must be stored as transportable binary XML to build a XML search index. For other storage types we can create an Oracle Text search index instead.
drop table if exists xml_tab purge;
create table xml_tab (
id number,
data xmltype
);
create search index xml_tab_search_idx on xml_tab (data);
◉ JSON Search Indexes
The original syntax for JSON search indexes in Oracle 12.1 was rather ugly.
drop table if exists t1 purge;
create table t1 (
id number,
data clob,
constraint t1_json_chk check (data is json)
);
create index t1_search_idx on t1 (data)
indextype is ctxsys.context
parameters ('section group ctxsys.json_section_group sync (on commit)');
In Oracle 12.2 the JSON search index syntax was simplified using the CREATE SEARCH INDEX ... FOR JSON syntax. The full syntax is available here.
drop index if exists t1_search_idx;
create search index t1_search_idx on t1 (data) for json;
The SEARCH_ON parameter defaults to TEXT_VALUE for XML search indexes.
- NONE : JSON queries are not supported. Only the data guide index is maintained.
- TEXT : Only text data is indexed for full-text search queries.
- VALUE(data_types) : Enables range-search for the specified data types. One or more of NUMBER, TIMESTAMP, VARCHAR2 as a comma-separated list.
- TEXT_VALUE(data_types) : A combination of support for full-text searches queries and range-search for the specified data types.
- TEXT_VALUE_STRING : A combination of support for full-text searches queries and range-search for NUMBER, TIMESTAMP and VARCHAR2 data types.
◉ Oracle Text Search Indexes
Oracle has a long history of full-text indexing for text data.
In Oracle 23c we can create full-text search indexes using the CREATE SEARCH INDEX syntax. The full syntax is available here.
drop table if exists text_tab purge;
create table text_tab (
id number,
data clob
);
create search index text_tab_search_idx on text_tab (data);
Source: oracle-base.com
0 comments:
Post a Comment