5.5.1.1 Configuring Text Indexes Using Oracle Text

Oracle Spatial and Graph supports automatic text indexes using Oracle Text. Oracle Text uses standard SQL to index, search, and analyze text values stored in the V column of the vertices (or edges) table. Because Oracle Text indexes all the existing K/V pairs of the vertices (or edges) in the property graph, this option can be used only with automatic text indexes and must use a wildcard ("*") indexed key parameter during the index creation.

Because the property graph feature uses an NVARCHAR typed column for a better support of Unicode, it is highly recommended that UTF8 (AL32UTF8) be used as the database character set.

To create an Oracle Text index on the vertices table (or edges table), the ALTER SESSION privilege is required. The following example grants the privilege.

SQL> grant alter session to <YOUR_USER_SCHEMA_HERE>;

If customization is required, grant EXECUTE on CTX_DDL, as in the following example.

SQL> grant execute on ctx_ddl to <YOUR_USER_SCHEMA_HERE>;

A text index using Oracle Text uses an OracleTextIndexParameters object. The configuration parameters for indexes using a Oracle Text include:

• Preference owner: the owner of the preference.

• Data store: the datastore preference specifying how the text values are stored. A datastore preference can be created using ctx_ddl.create_preference API as follows:

  SQL> -- The following requires access privilege to CTX_DDL
  SQL> exec ctx_ddl.create_preference('SCOTT.OPG_DATASTORE', 'DIRECT_DATASTORE');
  

  If the value is set to NULL, then the index will be created with CTXSYS.DEFAULT_DATASORE. This preference uses a DIRECT_DATASTORE type.

• Filter: the filter preference determining how text is filtered for indexing. A filter preference can be created using ctx_ddl.create_preference, as follows:

  SQL> -- The following requires access privilege to CTX_DDL
  SQL> exec ctx_ddl.create_preference('SCOTT.OPG_FILTER', 'AUTO_FILTER');
  

  If the value is set to NULL, then the index will be created with CTXSYS.NULL_FILTER. This preference uses a NULL_FILTER type.

• Storage: the storage preference specifying table space and creation parameters for tables associated with a Text index. A storage preference can be created using ctx_ddl.create_preference, as follows:

  SQL> -- The following requires access privilege to CTX_DDL
  SQL> exec ctx_ddl.create_preference('SCOTT.OPG_STORAGE', 'BASIC_STORAGE');
  

  If the value is set to NULL, then the index will be created with CTXSYS.DEFAULT_STORAGE. This preference uses a BASIC_STORAGE type.

• Word list: the word list preference specifying the enabled query options. These query options may include stemming, fuzzy matching, substring, and prefix indexing. A data store preference can be created using ctx_ddl.create_preference, as follows:

  SQL> -- The following example enables stemming and fuzzy matching for English.
  SQL> exec ctx_ddl.create_preference('SCOTT.OPG_WORDLIST', 'BASIC_WORDLIST');
  

  If the value is set to NULL, then the index will be created with CTXSYS.DEFAULT_WORDLIST. This preference uses the language stemmer for your database language.

• Stop list: the stop list preference specifying the list of words that are not meant to be indexed. A stop list preference can be created using ctx_ddl.create_stoplist .

  If the value is set to NULL, then the index will be created with CTXSYS.DEFAULT_STOPLIST. This preference uses the stoplist of your database language.

• Lexer: the lexer preference specifying the language of the text to be indexed. A lexer preference can be created using ctx_ddl.create_preference, as follows:

  SQL> -- The following requires access privilege to CTX_DDL
  SQL> exec ctx_ddl.create_preference('SCOTT.OPG_AUTO_LEXER', 'AUTO_LEXER');
  

  If the value is set to NULL, then the index will be created with CTXSYS.DEFAULT_LEXER. This preference uses a BASIC_LEXER type with additional options based on the language used at installation time.

The following code fragment creates the configuration for a text index using Oracle Text with default options and OPG_AUTO_LEXER.

String prefOwner = "scott";
String datastore = (String) null;
String filter = (String) null;
String storage = (String) null;
String wordlist = (String) null;
String stoplist = (String) null;
String lexer = "OPG_AUTO_LEXER";
String options = (String) null;

OracleIndexParameters params 
                  = OracleTextIndexParameters.buildOracleText(prefOwner,               
                                                              datastore, 
                                                              filter, 
                                                              storage, 
                                                              wordlist, 
                                                              stoplist, 
                                                              lexer, 
                                                              dop, 
                                                              options);