ADD_SOURCE

The DBMS_SEARCH.ADD_SOURCE procedure adds one or more data sources (tables or views) from different schemas to the DBMS_SEARCH index.

Syntax

DBMS_SEARCH.ADD_SOURCE (
index_name      VARCHAR2,
source_name     VARCHAR2,
memory          VARCHAR2 DEFAULT NULL,
parallel_degree NUMBER DEFAULT NULL);

Note: TheDBMS_SEARCH.ADD_SOURCE procedure has been upgraded in version 23.9 to support memory and parallel_degree parameters. These parameters are not available for use in earlier release versions.

index_name

Specify name of the index to which you want to add the table or view. You can also specify [schema].index_name.

source_name

Specify name of the table, view, or Duality view to add to the index. You can also specify [schema].table_or_view_name.

memory

Specify the amount of memory allocated for indexing of the added data source.

Note: Total memory usage is determined by multiplying the memory parameter by the parallel_degree.

parallel_degree

Specify the degree of parallelism used for indexing the added data source.

Notes

To add a data source, the index owner must have SELECT and DML access to the source. The user also must have the CTXAPP privilege.
All the data sources (such as table, view, or each table in the view definition) that are added to the DBMS_SEARCH index must include at least one Primary Key column. Each table that is part of a view source having a foreign key must also have the Foreign Key constraint, referencing the relevant primary keys defined on the table.

Defining a primary key for the source table is mandatory.
The DBMS_SEARCH index stores all supported SQL data types (including Object Type columns) in JSON objects, except for the XMLTYPE and LONG data types. This means that you cannot add a table or view as a data source to the index if it has a column with the XMLTYPE or LONG data type. The maximum allowed length of a JSON data type is 32 megabytes.
You can use the USER_DBMS_SEARCH_INDEX_SOURCES and ALL_DBMS_SEARCH_INDEX_SOURCES dictionary views to query information about the data sources that are added to your DBMS_SEARCH indexes.

Examples

exec DBMS_SEARCH.ADD_SOURCE('MYINDEX','MYTABLE');

exec DBMS_SEARCH.ADD_SOURCE('MYINDEX','MYVIEW');

exec DBMS_SEARCH.ADD_SOURCE('DOCUSER.MYINDEX','DOCUSER.MYTABLE');

Related Topics