CREATE_PATH_LIST

Creates a path-list preference to use with the PATHLIST parameter, when creating a path-subsetting JSON search index.

Understand Path Subsetting

You can use path subsetting to identify the fields in a document that you want to include or exclude from indexing. The excluded fields are not indexed, and the JSON search index is not used for them when querying. Filtering out irrelevant paths from documents can reduce the amount of data indexed, thereby minimizing disk space, indexing costs, and the index creation or rebuild time. As a result, the index maintenance operations improve in performance by skipping irrelevant data and saving further processing.

Consider using path subsetting if your documents include:

• A subset of paths that is frequently queried or is relevant for indexing

• Infrequently queried data or an irrelevant set of paths that can be ignored during indexing

As an alternative to specifying the INCLUDE or EXCLUDE clauses (as explained in CREATE SEARCH INDEX for JSON search index SEARCH_ON options), you can also use the PL/SQL subprograms CTX_DDL.CREATE_PATH_LIST and CTX_DDL.ADD_PATH to specify a subset of paths to include or exclude from indexing. You can then use the CTX_DDL.ADD_PATH API to add paths to this pathlist.

Syntax

begin
  CTX_DDL.CREATE_PATH_LIST(
    pref_name  IN    VARCHAR2,
    format     IN    NUMBER,
    behavior   IN    VARCHAR2
  );
end;

pref_name

Specify the name of the pathlist preference, which is a list of paths to include or exclude from indexing.

format

Specify the type of document that this pathlist should support. Currently, the only supported value is CTX_DDL.PATHLIST_JSON.

Note: You can enable path-subsetting for JSON search indexes only if the indexed column’s data-type is JSON type.

behavior

Specify the behavior of the pathlist. The values can be:

• CTX_DDL.PATHLIST_INCLUDE to create a list of path to be included for indexing

• CTX_DDL.PATHLIST_EXCLUDE to create a list of path to be excluded from indexing

This argument is optional. When omitted, the behavior value defaults to CTX_DDL.PATHLIST_INCLUDE.

Restrictions

• Specifying any SEARCH_ON clause while there is also a PATHLIST parameter results in an error. Similarly, you cannot specify a PATHLIST parameter for an index that has the Dataguide feature enabled.

• You can specify a path subsetting clause with SEARCH_ON TEXT, TEXT_VALUE, and VALUE (not with NONE and TEXT_VALUE_STRING).

• You cannot specify both the INCLUDE and EXCLUDE clauses for a single index.

Examples

• To create an INCLUDE pathlist:

  The following statement creates a JSON search index with path subsetting for full-text and string-equality searches of fields $.SpecialInstructions and $.LineItems.Part.Description of a purchase order document. But it also indexes fields $.PONumber and $.LineItems.Part.UnitPrice for numeric-value ranges, and fields $.Reference, $.User, $.ShippingInstructions.name, and $.ShippingInstructions.Address.zipCode for string-value ranges.

  CREATE SEARCH INDEX json_idx ON json_tab (purchase_order_jsondoc)
    FOR JSON PARAMETERS ('SEARCH_ON
  
      TEXT INCLUDE ($.SpecialInstructions, $.LineItems.Part.Description)
  
      VALUE(NUMBER) INCLUDE ($.PONumber, $.LineItems.Part.UnitPrice)
  
      VALUE(VARCHAR2) INCLUDE ($.Reference,
  
                               $.User,
                               $.ShippingInstructions.name,
                               $.ShippingInstructions.Address.zipCode)');

  Alternatively, you can create the same index using these pathlist APIs.

  Here, you first create a list of paths (json_pl_incl) to be included for indexing using the CTX_DDL.CREATE_PATH_LIST and CTX_DDL.ADD_PATH APIs. You then create an index using the PATHLIST parameter, whose value is a named list of the paths to be included.

  -- create a pathlist preference and add paths
  
  BEGIN
    CTX_DDL.create_path_list('json_pl_incl', CTX_DDL.PATHLIST_JSON, CTX_DDL.PATHLIST_INCLUDE);
    CTX_DDL.add_path('json_pl', 'TEXT',     '$.SpecialInstructions');
    CTX_DDL.add_path('json_pl', 'TEXT',     '$.LineItems.Part.Description');
    CTX_DDL.add_path('json_pl', 'NUMBER',   '$.PONumber');
    CTX_DDL.add_path('json_pl', 'NUMBER',   '$.LineItems.Part.UnitPrice');
    CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.Reference');
    CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.User');
    CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.ShippingInstructions.name');
    CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.ShippingInstructions.Address.zipCode');
  END;
  /
  
  -- declare the pathlist preference name
  
  CREATE SEARCH INDEX json_idx ON json_tab (purchase_order_jsondoc)
    FOR JSON PARAMETERS ('PATHLIST json_pl_incl');

• To create an EXCLUDE pathlist:

  Here, you first create a list of paths (json_pl_excl) to be excluded from indexing using the CTX_DDL.CREATE_PATH_LIST and CTX_DDL.ADD_PATH APIs. You then create an index using the PATHLIST parameter, whose value is a named list of the paths to be excluded.

  -- create a pathlist preference and add paths
  
  BEGIN
    CTX_DDL.create_path_list('json_pl_excl', CTX_DDL.PATHLIST_JSON, CTX_DDL.PATHLIST_EXCLUDE);
    CTX_DDL.add_path('json_pl_excl', 'TEXT',     '$.SpecialInstructions');
    CTX_DDL.add_path('json_pl_excl', 'TEXT',     '$.LineItems.Part.Description');
    CTX_DDL.add_path('json_pl_excl', 'NUMBER',   '$.PONumber');
    CTX_DDL.add_path('json_pl_excl', 'NUMBER',   '$.LineItems.Part.UnitPrice');
    CTX_DDL.add_path('json_pl_excl', 'VARCHAR2', '$.Reference');
    CTX_DDL.add_path('json_pl_excl', 'VARCHAR2', '$.User');
    CTX_DDL.add_path('json_pl_excl', 'VARCHAR2', '$.ShippingInstructions.name');
    CTX_DDL.add_path('json_pl_excl', 'VARCHAR2', '$.ShippingInstructions.Address.zipCode');
  END;
  /
  
  -- declare the pathlist preference name
  
  CREATE SEARCH INDEX json_idx ON json_tab (purchase_order_jsondoc)
    FOR JSON PARAMETERS ('PATHLIST json_pl_excl');

Related Topics

• CREATE SEARCH INDEX

• ADD_PATH

• DROP_PATH_LIST