OPG_APIS Package Subprograms

5.1 OPG_APIS.ANALYZE_PG

Format

OPG_APIS.ANALYZE_PG(
     graph_name       IN VARCHAR2,
     estimate_percent IN NUMBER,
     method_opt       IN VARCHAR2,
     degree           IN NUMBER,
     cascade          IN BOOLEAN,
     no_invalidate    IN BOOLEAN,
     force            IN BOOLEAN DEFAULT FALSE,
     options          IN VARCHAR2 DEFAULT NULL);

Description

Hathers, for a given property graph, statistics for the VT$, GE$, IT$, and GT$ tables.

Parameters

graph_name

Name of the property graph.

estimate_percent

Percentage of rows to estimate in the schema tables (NULL means compute). The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle Database determine the appropriate sample size for good statistics. This is the usual default.

mrthod_opt

Accepts either of the following options, or both in combination, for the internal property graph schema tables:

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

integer : Number of histogram buckets. Must be in the range [1,254].
REPEAT : Collects histograms only on the columns that already have histograms.
AUTO : Oracle Database determines the columns to collect histograms based on data distribution and the workload of the columns.
SKEWONLY : Oracle Database determines the columns to collect histograms based on the data distribution of the columns

column is defined as column := column_name | (extension)

column_name : name of a column
extension: Can be either a column group in the format of (column_name, column_name [, ...]) or an expression.

The usual default is: FOR ALL COLUMNS SIZE AUTO

degree

Degree of parallelism for the property graph schema tables. The usual default for degree is NULL, which means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.

cascade

Gathers statistics on the indexes for the property graph schema tables. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle Database determine whether index statistics are to be collected or not. This is the usual default.

no_invalidate

If TRUE, does not invalidate the dependent cursors. If FALSE, invalidates the dependent cursors immediately. If DBMS_STATS.AUTO_INVALIDATE (the usual default) is in effect, Oracle Database decides when to invalidate dependent cursors.

force

If TRUE, performs the operation even if one or more underlying tables are locked.

options

(Reserved for future use.)

Usage Notes

Only the owner of the property graph can call this procedure.

Examples

The following example gather statistics for property graph mypg.

EXECUTE OPG_APIS.ANALYZE_PG('mypg', estimate_percent=> 0.001, method_opt=>'FOR ALL COLUMNS SIZE AUTO', degree=>4, cascade=>true, no_invalidate=>false, force=>true, options=>NULL);

5.2 OPG_APIS.CF

Format

OPG_APIS.CF(
     edge_tab_name   IN      VARCHAR2,
     edge_label      IN      VARCHAR2,
     rating_property IN      VARCHAR2,
     iterations      IN      NUMBER DEFAULT 10,
     min_error       IN      NUMBER DEFAULT 0.001,
     k               IN      NUMBER DEFAULT 5,
     learning_rate   IN      NUMBER DEFAULT 0.0002,
     decrease_rate   IN      NUMBER DEFAULT 0.95,
     regularization  IN      NUMBER DEFAULT 0.02,
     dop             IN      NUMBER DEFAULT 8,
     wt_l            IN/OUT  VARCHAR2, 
     wt_r            IN/OUT  VARCHAR2, 
     wt_l1           IN/OUT  VARCHAR2, 
     wt_r1           IN/OUT  VARCHAR2, 
     wt_i            IN/OUT  VARCHAR2, 
     wt_ld           IN/OUT  VARCHAR2, 
     wt_rd           IN/OUT  VARCHAR2, 
     tablespace      IN      VARCHAR2 DEFAULT NULL,
     options         IN      VARCHAR2 DEFAULT NULL);

Description

Runs collaborative filtering using matrix factorization on the given graph. The resulting factors of the matrix product will be stored on the left and right tables.

Parameters

edge_tab_name

Name of the property graph edge table (GE$).

edge_label

Label of the edges that hold the rating property.

rating_property

(Reserved for future use: Name of the rating property.)

iterations

Maximum number of iterations that should be performed. Default = 10.

min_error

Minimal error to reach. If at some iteration the error value is lower than this value, the procedure finishes.. Default = 0.001.

k

Number of features for the left and right side products. Default = 5.

learning_rate

Learning rate for the gradient descent. Default = 0.0002.

decrease_rate

(Reserved for future use: Decrease rate if the learning rate is too large for an effective gradient descent. Default = 0.95.)

regularization

An additional parameter to avoid overfitting. Default = 0.02

dop

Degree of parallelism. Default = 8.

wt_l

Name of the working table that holds the left side of the matrix factorization.

wt_r

Name of the working table that holds the right side of the matrix factorization.

wt_l1

Name of the working table that holds the left side intermediate step in the gradient descent.

wt_r1

Name of the working table that holds the right side intermediate step in the gradient descent.

wt_I

Name of the working table that holds intermediate matrix product.

wt_ld

Name of the working table that holds intermediate left side delta in gradient descent.

wt_rd

Name of the working table that holds intermediate right side delta in gradient descent.

tablespace

Name of the tablespace to use for storing intermediate data.

options

Additional settings for operation. An optional string with one or more (comma-separated) of the following values:

'INMEMORY=T' is an option for creating the schema tables with an 'inmemory' clause.
'IMC_MC_B=T' creates the schema tables with an INMEMORY MEMCOMPRESS BASIC clause.

Usage Notes

For information about collaborative filtering with RDF data, see SQL-Based Property Graph Analytics, especially Collaborative Filtering Overview and Examples.

If the working tables already exist, you can specify their names for the working table-related parameters. In this case, the algorithm can continue the progress of the previous iterations without recreating the tables.

If the working tables do not exist, or if you do not want to use existing working tables, you must first call the OPG_APIS.CF_PREP procedure, which creates the necessary working tables.

The final result of the collaborative filtering algorithm are the working tables wt_l and wt_r, which are the two factors of a matrix product. These matrix factors should be used when making predictions for collaborative filtering.

If (and only if) you have no interest in keeping the output matrix factors and the current progress of the algorithm for future use, you can call the OPG_APIS.CF_CLEANUP procedure to drop all the working tables that hold intermediate tables and the output matrix factors.

Examples

The following example calls the OPG_APIS.CF_PREP procedure to create the working tables, and then the OPG_APIS.CF procedures to run collaborative filtering on the phones graph using the edges with the rating label.

DECLARE
  wt_l varchar2(32);
  wt_r varchar2(32);
  wt_l1 varchar2(32);
  wt_r1 varchar2(32);
  wt_i varchar2(32);
  wt_ld varchar2(32);
  wt_rd varchar2(32);
  edge_tab_name    varchar2(32) := 'phonesge$';
  edge_label       varchar2(32) := 'rating';
  rating_property  varchar2(32) := '';
  iterations       integer      := 100;
  min_error        number       := 0.001;
  k                integer      := 5;
  learning_rate    number       := 0.001;
  decrease_rate    number       := 0.95;
  regularization   number       := 0.02;
  dop              number       := 2;
  tablespace       varchar2(32) := null;
  options          varchar2(32) := null; 
BEGIN
 opg_apis.cf_prep(edge_tab_name,wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd);
 opg_apis.cf(edge_tab_name,edge_label,rating_property,iterations,min_error,k,
             learning_rate,decrease_rate,regularization,dop,
             wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd,tablespace,options);
END;
/

The following example assumes that OPG_APIS.CF_PREP had been run previously, and it specifies the various working tables that were created during that run. In this case, the preceding example automatically assigned suffixes like '$$CFL57' to the names of the working tables. (The output names can be printed when they are generated or be user-defined in the call to OPG_APIS.CF_PREP.) Thus, the following example can run more iterations of the algorithm using OPG_APIS.CF without needing to call OPG_APIS.CF_PREP first, thereby continuing the progress of the previous run.

DECLARE
  wt_l varchar2(32)  = 'phonesge$$CFL57';
  wt_r varchar2(32)  = 'phonesge$$CFR57';
  wt_l1 varchar2(32) = 'phonesge$$CFL157';
  wt_r1 varchar2(32) = 'phonesge$$CFR157';
  wt_i varchar2(32)  = 'phonesge$$CFI57';
  wt_ld varchar2(32) = 'phonesge$$CFLD57';
  wt_rd varchar2(32) = 'phonesge$$CFRD57';
  edge_tab_name    varchar2(32) := 'phonesge$';
  edge_label       varchar2(32) := 'rating';
  rating_property  varchar2(32) := '';
  iterations       integer      := 100;
  min_error        number       := 0.001;
  k                integer      := 5;
  learning_rate    number       := 0.001;
  decrease_rate    number       := 0.95;
  regularization   number       := 0.02;
  dop              number       := 2;
  tablespace       varchar2(32) := null;
  options          varchar2(32) := null; 
BEGIN
 opg_apis.cf(edge_tab_name,edge_label,rating_property,iterations,min_error,k,
             learning_rate,decrease_rate,regularization,dop,
             wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd,tablespace,options);
END;
/

5.3 OPG_APIS.CF_CLEANUP

Format

OPG_APIS.CF_CLEANUP(
     wt_l            IN/OUT  VARCHAR2, 
     wt_r            IN/OUT  VARCHAR2, 
     wt_l1           IN/OUT  VARCHAR2, 
     wt_r1           IN/OUT  VARCHAR2, 
     wt_i            IN/OUT  VARCHAR2, 
     wt_ld           IN/OUT  VARCHAR2, 
     wt_rd           IN/OUT  VARCHAR2, 
     options         IN      VARCHAR2 DEFAULT NULL);

Description

Preforms cleanup work after graph collaborative filtering has been done. All the working tables that hold intermediate tables and the output matrix factors are dropped.

Parameters

edge_tab_name: Name of the property graph edge table (GE$).
wt_l: Name of the working table that holds the left side of the matrix factorization.
wt_r: Name of the working table that holds the right side of the matrix factorization.
wt_l1: Name of the working table that holds the left side intermediate step in the gradient descent.
wt_r1: Name of the working table that holds the right side intermediate step in the gradient descent.
wt_I: Name of the working table that holds intermediate matrix product.
wt_ld: Name of the working table that holds intermediate left side delta in gradient descent.
wt_rd: Name of the working table that holds intermediate right side delta in gradient descent.
options: (Reserved for future use.)

Usage Notes

Call this procedure only when you have no interest in keeping the output matrix factors and the current progress of the algorithm for future use.

Do not call this procedure if more predictions will be made using the resulting product factors (wt_l and wt_r tables), unless you have previous made backup copies of these two tables.

See also the information about the OPG_APIS.CF procedure.

Examples

The following example drops the working tables that were created in the example for the OPG_APIS.CF_PREP procedure.

DECLARE
  wt_l varchar2(32)  = 'phonesge$$CFL57';
  wt_r varchar2(32)  = 'phonesge$$CFR57';
  wt_l1 varchar2(32) = 'phonesge$$CFL157';
  wt_r1 varchar2(32) = 'phonesge$$CFR157';
  wt_i varchar2(32)  = 'phonesge$$CFI57';
  wt_ld varchar2(32) = 'phonesge$$CFLD57';
  wt_rd varchar2(32) = 'phonesge$$CFRD57';
BEGIN
  opg_apis.cf_cleanup('phonesge$',wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd);
END;
/

5.4 OPG_APIS.CF_PREP

Format

OPG_APIS.CF_PREP(
     wt_l            IN/OUT  VARCHAR2. 
     wt_r            IN/OUT  VARCHAR2. 
     wt_l1           IN/OUT  VARCHAR2. 
     wt_r1           IN/OUT  VARCHAR2. 
     wt_i            IN/OUT  VARCHAR2. 
     wt_ld           IN/OUT  VARCHAR2. 
     wt_rd           IN/OUT  VARCHAR2. 
     options         IN      VARCHAR2 DEFAULT NULL);

Description

Preforms preparation work, including creating the necessary intermediate tables, for a later call to the OPG_APIS.CF procedure that will perform collaborative filtering.

Parameters

edge_tab_name

Name of the property graph edge table (GE$).

wt_l

Name of the working table that holds the left side of the matrix factorization.

wt_r

Name of the working table that holds the right side of the matrix factorization.

wt_l1

Name of the working table that holds the left side intermediate step in the gradient descent.

wt_r1

Name of the working table that holds the right side intermediate step in the gradient descent.

wt_I

Name of the working table that holds intermediate matrix product.

wt_ld

Name of the working table that holds intermediate left side delta in gradient descent.

wt_rd

Name of the working table that holds intermediate right side delta in gradient descent.

options

Additional settings for operation. An optional string with one or more (comma-separated) of the following values:

'INMEMORY=T' is an option for creating the schema tables with an 'inmemory' clause.
'IMC_MC_B=T' creates the schema tables with an INMEMORY MEMCOMPRESS BASIC clause.

Usage Notes

The names of the working tables can be specified or left as null parameters, If the name of any working table parameter is not specified, a name is automatically genenerated and is returned as an OUT parameter. The working table names can be used when you call the OPG_APIS.CF procedure to run the collaborative filtering algorithm.

See also the Usage Notes and Examples for OPG_APIS.CF.

Examples

The following example creates the working tables for a graph named phones, and it prints the names that were automatically generated for the working tables.

DECLARE
  wt_l varchar2(32);
  wt_r varchar2(32);
  wt_l1 varchar2(32);
  wt_r1 varchar2(32);
  wt_i varchar2(32);
  wt_ld varchar2(32);
  wt_rd varchar2(32);
BEGIN
  opg_apis.cf_prep('phonesge$',wt_l,wt_r,wt_l1,wt_r1,wt_i,wt_ld,wt_rd);
  dbms_output.put_line(' wt_l ' || wt_l);
  dbms_output.put_line(' wt_r ' || wt_r);
  dbms_output.put_line(' wt_l1 ' || wt_l1);
  dbms_output.put_line(' wt_r1 ' || wt_r1);
  dbms_output.put_line(' wt_i ' || wt_i);
  dbms_output.put_line(' wt_ld ' || wt_ld);
  dbms_output.put_line(' wt_rd ' || wt_rd);
END;
/

5.5 OPG_APIS.CLEAR_PG

Format

OPG_APIS.CLEAR_PG(
     graph_name  IN VARCHAR2);

Description

Clears all data from a property graph.

Parameters

graph_name: Name of the property graph.

Usage Notes

This procedure removes all data in the property graph by deleting data in the graph tables (VT$, GE$, and so on).

Examples

The following example removes all data from the property graph named mypg.

EXECUTE OPG_APIS.CLEAR_PG('mypg');

5.6 OPG_APIS.CLEAR_PG_INDICES

Format

OPG_APIS.CLEAR_PG(
     graph_name  IN VARCHAR2);

Description

Removes all text index metadata in the IT$ table of the property graph.

Parameters

graph_name: Name of the property graph.

Usage Notes

This procedure does not actually remove text index data

Examples

The following example removes all index metadata of the property graph named mypg.

EXECUTE OPG_APIS.CLEAR_PG_INDICES('mypg');

5.7 OPG_APIS.CLONE_GRAPH

Format

OPG_APIS.CLONE_GRAPH(
     orgGraph      IN VARCHAR2,
     newGraph      IN VARCHAR2,
     dop           IN INTEGER DEFAULT 4,
     num_hash_ptns IN INTEGER DEFAULT 8,
     tbs           IN VARCHAR2 DEFAULT NULL);

Description

Makes a clone of the original graph, giving the new graph a new name.

Parameters

orgGraph: Name of the original property graph.
newGraph: Name of the new (clone) property graph.
dop: Degree of parallelism for the operation.
num_hash_ptns: Number of hash partitions used to partition the vertices and edges tables. It is recommended to use a power of 2 (2, 4, 8, 16, and so on).
tbs: Name of the tablespace to hold all the graph data and index data.

Usage Notes

The original property graph must aleady exist in the database.

Examples

The following example creates a clone graph named mypgclone from the property graph mypg in the tablespace my_ts using a degree of parallelism of 4 and 8 partitions.

EXECUTE OPG_APIS.CLONE_GRAPH('mypg', 'mypgclone', 4, 8, 'my_ts');

5.8 OPG_APIS.COUNT_TRIANGLE

Format

OPG_APIS.COUNT_TRIANGLE(
     edge_tab_name IN VARCHAR2,
     wt_und        IN OUT VARCHAR2,
     num_sub_ptns  IN NUMBER DEFAULT 1,
     dop           IN INTEGER DEFAULT 1,
     tbs           IN VARCHAR2 DEFAULT NULL,
     options       IN VARCHAR2 DEFAULT NULL
) RETURN NUMBER;

Description

Performs triangle counting in property graph.

Parameters

edge_tab_name

Name of the property graph edge table.

wt_und

A working table holding an undirected version of the graph.

num_sub_ptns

Number of logical subpartitions used in calculating triangles . Must be a positive integer, power of 2 (1, 2, 4, 8, ...). For a graph with a relatively small maximum degree, use the value 1 (the default).

dop

Degree of parallelism for the operation. The default is 1.

tbs

Name of the tablespace to hold the data stored in working tables.

options

Additional settings for the operation:

’PDML=T' enables parallel DML.

Usage Notes

The property graph edge table must exist in the database, and the OPG_APIS.COUNT_TRIANGLE_PREP. procedure must already have been executed.

Examples

The following example performs triangle counting in the property graph named connections

set serveroutput on
DECLARE
  wt1 varchar2(100);  -- intermediate working table
  wt2 varchar2(100);
  wt3 varchar2(100);
  n number;
BEGIN
  opg_apis.count_triangle_prep('connectionsGE$', wt1, wt2, wt3);
  n := opg_apis.count_triangle(
     'connectionsGE$',
      wt1,
      num_sub_ptns=>1,
      dop=>2,
      tbs => 'MYPG_TS',
      options=>'PDML=T'
      ); 
  dbms_output.put_line('total number of triangles ' || n);
END;
/

5.9 OPG_APIS.COUNT_TRIANGLE_CLEANUP

Format

COUNT_TRIANGLE_CLEANUP(
   edge_tab_name IN VARCHAR2,
   wt_undBM      IN VARCHAR2,
   wt_rnmap      IN VARCHAR2,
   wt_undAM      IN VARCHAR2,
   options       IN VARCHAR2 DEFAULT NULL);

Description

Cleans up and drops the temporary working tables used for triangle counting.

Parameters

edge_tab_name

Name of the property graph edge table.

wt_undBM

A working table holding an undirected version of the original graph (before renumbering optimization).

wt_rnmap

A working table that is a mapping table for renumbering optimization.

wt_undAM

A working table holding the undirected version of the graph data after applying the renumbering optimization.

options

Additional settings for operation. An optional string with one or more (comma-separated) of the following values:

PDML=T enables parallel DML.

Usage Notes

You should use this procedure to clean up after triangle counting.

The working tables must exist in the database.

Examples

The following example performs triangle counting in the property graph named connections, and drops the working table after it has finished.

set serveroutput on

DECLARE
  wt1 varchar2(100);  -- intermediate working table
  wt2 varchar2(100);
  wt3 varchar2(100);
  n number;
BEGIN
  opg_apis.count_triangle_prep('connectionsGE$', wt1, wt2, wt3);
  n := opg_apis.count_triangle_renum(
     'connectionsGE$',
      wt1,
      wt2,
      wt3,
      num_sub_ptns=>1,
      dop=>2,
      tbs => 'MYPG_TS',
      options=>'PDML=T'
      ); 
  dbms_output.put_line('total number of triangles ' || n);
  opg_apis.count_triangle_cleanup('connectionsGE$', wt1, wt2, wt3);
END;
/

5.10 OPG_APIS.COUNT_TRIANGLE_PREP

Format

OPG_APIS.COUNT_TRIANGLE_PREP(
     edge_tab_name  IN VARCHAR2,
     wt_undBM       IN OUT VARCHAR2,
     wt_rnmap       IN OUT VARCHAR2,
     wt_undAM       IN OUT VARCHAR2,
     options        IN VARCHAR2 DEFAULT NULL);

Description

Prepares for running triangle counting.

Parameters

edge_tab_name

Name of the property graph edge table.

wt_undBM

A working table holding an undirected version of the original graph (before renumbering optimization).

wt_rnmap

A working table that is a mapping table for renumbering optimization.

wt_undAM

A working table holding the undirected version of the graph data after applying the renumbering optimization.

options

Additional settings for operation. An optional string with one or more (comma-separated) of the following values:

CREATE_UNDIRECTED=T
REUSE_UNDIRECTED_TAB=T

Usage Notes

The property graph edge table must exist in the database.

Examples

The following example prepares for triangle counting in a property graph named connections.

set serveroutput on

DECLARE
  wt1 varchar2(100);  -- intermediate working table
  wt2 varchar2(100);
  wt3 varchar2(100);
  n number;
BEGIN
  opg_apis.count_triangle_prep('connectionsGE$', wt1, wt2, wt3);

  n := opg_apis.count_triangle_renum(
     'connectionsGE$',
      wt1,
      wt2,
      wt3,
      num_sub_ptns=>1,
      dop=>2,
      tbs => 'MYPG_TS',
      options=>'CREATE_UNDIRECTED=T,REUSE_UNDIREC_TAB=T'
      ); 
  dbms_output.put_line('total number of triangles ' || n);
END;
/

5.11 OPG_APIS.COUNT_TRIANGLE_RENUM

Format

COUNT_TRIANGLE_RENUM(
   edge_tab_name IN VARCHAR2,
   wt_undBM      IN VARCHAR2,
   wt_rnmap      IN VARCHAR2,
   wt_undAM      IN VARCHAR2,
   num_sub_ptns  IN INTEGER DEFAULT 1,
   dop           IN INTEGER DEFAULT 1,
   tbs           IN VARCHAR2 DEFAULT NULL,
   options       IN VARCHAR2 DEFAULT NULL
   ) RETURN NUMBER;

Description

Performs triangle counting in property graph, with the optimization of renumbering the vertices of the graph by their degree.

Parameters

edge_tab_name

Name of the property graph edge table.

wt_undBM

A working table holding an undirected version of the original graph (before renumbering optimization).

wt_rnmap

A working table that is a mapping table for renumbering optimization.

wt_undAM

A working table holding the undirected version of the graph data after applying the renumbering optimization.

num_sub_ptns

Number of logical subpartitions used in calculating triangles . Must be a positive integer, power of 2 (1, 2, 4, 8, ...). For a graph with a relatively small maximum degree, use the value 1 (the default).

dop

Degree of parallelism for the operation. The default is 1 (no parallelism).

tbs

Name of the tablespace to hold the data stored in working tables.

options

Additional settings for operation. An optional string with one or more (comma-separated) of the following values:

PDML=T enables parallel DML.

Usage Notes

This function makes the algorithm run faster, but requires more space.

The property graph edge table must exist in the database, and the OPG_APIS.COUNT_TRIANGLE_PREP procedure must already have been executed.

Examples

The following example performs triangle counting in the property graph named connections. It does not perform the cleanup after it finishes, so you can count triangles again on the same graph without calling the preparation procedure.

set serveroutput on

DECLARE
  wt1 varchar2(100);  -- intermediate working table
  wt2 varchar2(100);
  wt3 varchar2(100);
  n number;
BEGIN
  opg_apis.count_triangle_prep('connectionsGE$', wt1, wt2, wt3);
  n := opg_apis.count_triangle_renum(
     'connectionsGE$',
      wt1,
      wt2,
      wt3,
      num_sub_ptns=>1,
      dop=>2,
      tbs => 'MYPG_TS',
      options=>'PDML=T'
      ); 
  dbms_output.put_line('total number of triangles ' || n);
END;
/

5.12 OPG_APIS.CREATE_EDGES_TEXT_IDX

Format

OPG_APIS.CREATE_EDGES_TEXT_IDX(
     graph_owner IN VARCHAR2,
     graph_name  IN VARCHAR2,
     pref_owner  IN VARCHAR2 DEFAULT NULL,
     datastore   IN VARCHAR2 DEFAULT NULL,
     filter      IN VARCHAR2 DEFAULT NULL,
     storage     IN VARCHAR2 DEFAULT NULL,
     wordlist    IN VARCHAR2 DEFAULT NULL,
     stoplist    IN VARCHAR2 DEFAULT NULL,
     lexer       IN VARCHAR2 DEFAULT NULL,
     dop         IN INTEGER DEFAULT NULL,
     options     IN VARCHAR2 DEFAULT NULL,);

Description

Creates a text index on a property graph edge table.

Parameters

graph_owner: Owner of the property graph.
graph_name: Name of the property graph.
pref_owner: Owner of the preference.
datastore: The way that documents are stored.
filter: The way that documents can be converted to plain text.
storage: The way that the index data is stored.
wordlist: The way that stem and fuzzy queries should be expanded
stoplist: The words or themes that are not to be indexed.
lexer: The language used for indexing.
dop: The degree of parallelism used for index creation.
options: Additional settings for index creation.

Usage Notes

The property graph must exist in the database.

You must have the ALTER SESSION privilege to run this procedure.

Examples

The following example creates a text index on the edge table of property graph mypg, which is owned by user SCOTT, using the lexer OPG_AUTO_LEXER and a degree of parallelism of 4.

EXECUTE OPG_APIS.CREATE_EDGES_TEXT_IDX('SCOTT', 'mypg', 'MDSYS', null, null, null, null, null, 'OPG_AUTO_LEXER', 4, null);

5.13 OPG_APIS.CREATE_PG

Format

OPG_APIS.CREATE_PG(
     graph_name    IN VARCHAR2,
     dop           IN INTEGER DEFAULT NULL,
     num_hash_ptns IN INTEGER DEFAULT 8,
     tbs           IN VARCHAR2 DEFAULT NULL,
     options       IN VARCHAR2 DEFAULT NULL);

Description

Creates, for a given property graph name, the necessary property graph schema tables that are necessary to store data about vertices, edges, text indexes, and snapshots.

Parameters

graph_name

Name of the property graph.

dop

Degree of parallelism for the operation.

num_hash_ptns

Number of hash partitions used to partition the vertices and edges tables. It is recommended to use a power of 2 (2, 4, 8, 16, and so on).

tbs

Name of the tablespace to hold all the graph data and index data.

options

Options that can be used to customize the creation of indexes on schema tables. (One or more, comma separated.)

'SKIP_INDEX=T' skips the default index creation.
'SKIP_ERROR=T 'ignores errors encountered during table/index creation.
'INMEMORY=T' creqtes the schema tables with an INMEMORYclause.
'IMC_MC_B=T' creates the schema tables with an INMEMORY BASIC clause.

Usage Notes

You must have the CREATE TABLE and CREATE INDEX privileges to call this procedure.

By default, all the schema tables will be created with basic compression enabled.

Examples

The following example creates a property graph named mypg in the tablespace my_ts using eight partitions.

EXECUTE OPG_APIS.CREATE_PG('mypg', 4, 8, 'my_ts');

5.14 OPG_APIS.CREATE_PG_SNAPSHOT_TAB

Format

OPG_APIS.CREATE_PG_SNAPSHOT_TAB(
     graph_owner IN VARCHAR2,
     graph_name  IN VARCHAR2,
     dop         IN INTEGER DEFAULT NULL,
     tbs         IN VARCHAR2 DEFAULT NULL,
     options     IN VARCHAR2 DEFAULT NULL);

or

OPG_APIS.CREATE_PG_SNAPSHOT_TAB(
     graph_name  IN VARCHAR2,
     dop         IN INTEGER DEFAULT NULL,
     tbs         IN VARCHAR2 DEFAULT NULL,
     options     IN VARCHAR2 DEFAULT NULL);

Description

Creates, for a given property graph name, the necessary property graph schema table (<graph_name>SS$) that stores data about snapshots for the graph.

Parameters

graph_owner

Name of the owner of the property graph.

graph_name

Name of the property graph.

dop

Degree of parallelism for the operation.

tbs

Name of the tablespace to hold all the graph snapshot data and associated index.

options

Additional settings for the operation:

'INMEMORY=T' is an option for creating the schema tables with an 'inmemory' clause.
'IMC_MC_B=T' creates the schema tables with an INMEMORY MEMCOMPRESS BASIC clause.

Usage Notes

You must have the CREATE TABLE privilege to call this procedure.

The created snapshot table has the following structure, which may change between releases.

Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 SSID                       NOT NULL NUMBER
 CONTENTS                            BLOB
 SS_FILE                             BINARY FILE LOB
 TS                                  TIMESTAMP(6) WITH TIME ZONE
 SS_COMMENT                          VARCHAR2(512)

By default, all schema tables will be created with basic compression enabled.

Examples

The following example creates a snapshot table for property graph mypg in the current schema, with a degree of parallelism of 4 and using the MY_TS tablespace.

EXECUTE OPG_APIS.CREATE_PG_SNAPSHOT_TAB('mypg', 4, 'my_ts');

5.15 OPG_APIS.CREATE_PG_TEXTIDX_TAB

Format

OPG_APIS.CREATE_PG_TEXTIDX_TAB(
     graph_owner IN VARCHAR2,
     graph_name  IN VARCHAR2,
     dop         IN INTEGER DEFAULT NULL,
     tbs         IN VARCHAR2 DEFAULT NULL,
     options     IN VARCHAR2 DEFAULT NULL);

or

OPG_APIS.CREATE_PG_TEXTIDX_TAB(
     graph_name  IN VARCHAR2,
     dop         IN INTEGER DEFAULT NULL,
     tbs         IN VARCHAR2 DEFAULT NULL,
     options     IN VARCHAR2 DEFAULT NULL);

Description

Creates, for a given property graph name, the necessary property graph text index schema table (<graph_name>IT$) that stores data for managing text index metadata for the graph.

Parameters

graph_owner

Name of the owner of the property graph.

graph_name

Name of the property graph.

dop

Degree of parallelism for the operation.

tbs

Name of the tablespace to hold all the graph index metadata and associated index.

options

Additional settings for the operation:

'INMEMORY=T' is an option for creating the schema tables with an 'inmemory' clause.
'IMC_MC_B=T' creates the schema tables with an INMEMORY MEMCOMPRESS BASIC clause.

Usage Notes

You must have the CREATE TABLE privilege to call this procedure.

The created index metadata table has the following structure, which may change between releases.

          (
             EIN     nvarchar2(80) not null,  -- index name
             ET      number,                  -- entity type 1 - vertex, 2 -edge 
             IT      number,                  -- index type 1 - auto   0 - manual
             SE      number,                  -- search engine 1 -solr, 0 - lucene
             K       nvarchar2(3100),         -- property key use an empty space when there is no K/V
             DT      number,                  -- directory type 1 - MMAP, 2 - FS, 3 - JDBC 
             LOC     nvarchar2(3100),         -- directory location (1, 2)
             NUMDIRS number,                  -- property key used to index CAN BE NULL
             VERSION nvarchar2(100),          -- lucene version
             USEDT   number,                  -- user data type (1 or 0)
             STOREF  number,                  -- store fields into lucene
             CF      nvarchar2(3100),          -- configuration name
             SS      nvarchar2(3100),          -- solr server url
             SA      nvarchar2(3100),          -- solr server admin url
             ZT      number,                  -- zookeeper timeout
             SH      number,                  -- number of shards
             RF      number,                  -- replication factor
             MS      number,                  -- maximum shards per node
             PO      nvarchar2(3100),         -- preferred owner oracle text
             DS      nvarchar2(3100),         -- datastore 
             FIL     nvarchar2(3100),         -- filter
             STR     nvarchar2(3100),         -- storage
             WL      nvarchar2(3100),         -- word list
             SL      nvarchar2(3100),         -- stop list
             LXR     nvarchar2(3100),         -- lexer
             OPTS    nvarchar2(3100),         -- options
             primary key (EIN, K, ET)  
          )

By default, all schema tables will be created with basic compression enabled.

Examples

The following example creates a property graph text index metadata table for property graph mypg in the current schema, with a degree of parallelism of 4 and using the MY_TS tablespace.

EXECUTE OPG_APIS.CREATE_PG_TEXTIDX_TAB('mypg', 4, 'my_ts');

5.16 OPG_APIS.CREATE_STAT_TABLE

Format

OPG_APIS.CREATE_STAT_TABLE(
     stattab   IN VARCHAR2,
     tblspace  IN VARCHAR2 DEFAULT NULL);

Description

Creates a table that can hold property graph statistics.

Parameters

stattab: Name of the table to hold statistics
tblapace: Name of the tablespace to hold the statistics table. If none is specified, then the statistics table will be created in the user's default tablespace.

Usage Notes

You must have the CREATE TABLE privilege to call this procedure.

The statistics table has the following columns. Note that the columns and their types may vary between releases.

 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATID 					    VARCHAR2(128)
 TYPE						    CHAR(1)
 VERSION					    NUMBER
 FLAGS						    NUMBER
 C1						    VARCHAR2(128)
 C2						    VARCHAR2(128)
 C3						    VARCHAR2(128)
 C4						    VARCHAR2(128)
 C5						    VARCHAR2(128)
 C6						    VARCHAR2(128)
 N1						    NUMBER
 N2						    NUMBER
 N3						    NUMBER
 N4						    NUMBER
 N5						    NUMBER
 N6						    NUMBER
 N7						    NUMBER
 N8						    NUMBER
 N9						    NUMBER
 N10						    NUMBER
 N11						    NUMBER
 N12						    NUMBER
 N13						    NUMBER
 D1						    DATE
 T1						    TIMESTAMP(6) WITH TIME ZONE
 R1						    RAW(1000)
 R2						    RAW(1000)
 R3						    RAW(1000)
 CH1						    VARCHAR2(1000)
 CL1						    CLOB

Examples

The following example creates a statistics table namedmystat .

EXECUTE OPG_APIS.CREATE_STAT_TABLE('mystat',null);

5.17 OPG_APIS.CREATE_SUB_GRAPH

Format

OPG_APIS.CREATE_SUB_GRAPH(
     graph_owner IN VARCHAR2,
     orgGraph    IN VARCHAR2,
     newGraph    IN VARCHAR2,
     nSrc        IN NUMBER,
     depth       IN NUMBER);

Description

Creates a subgraph, which is an expansion from a given vertex. The depth of expansion is customizable.

Parameters

graph_owner: Owner of the property graph.
orgGraph: Name of the original property graph.
newGraph: Name of the subgraph to be created from the original graph.
nSrc: Vertex ID: the subgraph will be created by expansion from this vertex. For example, nSrc = 1 starts the expansion from the vertex with ID 1.
depth: Depth of expansion: the expansion, following outgoing edges, will include all vertices that are within depth hops away from vertex nSrc. For example, depth = 2 causes the to should include all vertices that are within 2 hops away from vertex nSrc (vertex ID 1 in the preceding example).

Usage Notes

The original property graph must exist in the database.

Examples

The following example creates a subgraph mypgsub from the property graph mypg whose owner is SCOTT. The subgraph includes vertex 1 and all vertices that are reachable from the vertex with ID 1 in 2 hops.

EXECUTE OPG_APIS.CREATE_SUB_GRAPH('SCOTT', 'mypg', 'mypgsub', 1, 2);

5.18 OPG_APIS.CREATE_VERTICES_TEXT_IDX

Format

OPG_APIS.CREATE_VERTICES_TEXT_IDX(
     graph_owner IN VARCHAR2,
     graph_name  IN VARCHAR2,
     pref_owner  IN VARCHAR2 DEFAULT NULL,
     datastore   IN VARCHAR2 DEFAULT NULL,
     filter      IN VARCHAR2 DEFAULT NULL,
     storage     IN VARCHAR2 DEFAULT NULL,
     wordlist    IN VARCHAR2 DEFAULT NULL,
     stoplist    IN VARCHAR2 DEFAULT NULL,
     lexer       IN VARCHAR2 DEFAULT NULL,
     dop         IN INTEGER DEFAULT NULL,
     options     IN VARCHAR2 DEFAULT NULL,);

Description

Creates a text index on a property graph vertex table.

Parameters

graph_owner: Owner of the property graph.
graph_name: Name of the property graph.
pref_owner: Owner of the preference.
datastore: The way that documents are stored.
filter: The way that documents can be converted to plain text.
storage: The way that the index data is stored.
wordlist: The way that stem and fuzzy queries should be expanded
stoplist: The words or themes that are not to be indexed.
lexer: The language used for indexing.
dop: The degree of parallelism used for index creation.
options: Additional settings for index creation.

Usage Notes

The original property graph must exist in the database.

You must have the ALTER SESSION privilege to run this procedure.

Examples

The following example creates a text index on the vertex table of property graph mypg, which is owned by user SCOTT, using the lexer OPG_AUTO_LEXER and a degree of parallelism of 4.

EXECUTE OPG_APIS.CREATE_VERTICES_TEXT_IDX('SCOTT', 'mypg', null, null, null, null, null, null, 'OPG_AUTO_LEXER', 4, null);

5.19 OPG_APIS.DROP_EDGES_TEXT_IDX

Format

OPG_APIS.DROP_EDGES_TEXT_IDX(
     graph_owner IN VARCHAR2,
     graph_name  IN VARCHAR2,
     options     IN VARCHAR2 DEFAULT NULL);

Description

Drops a text index on a property graph edge table.

Parameters

graph_owner: Owner of the property graph.
graph_name: Name of the property graph.
options: Additional settings for the operation.

Usage Notes

A text index must already exist on the property graph edge table.

Examples

The following example drops the text index on the edge table of property graph mypg that is owned by user SCOTT.

EXECUTE OPG_APIS.DROP_EDGES_TEXT_IDX('SCOTT', 'mypg', null);

5.20 OPG_APIS.DROP_PG

Format

OPG_APIS.DROP_PG(
     graph_name  IN VARCHAR2);

Description

Drops (deletes) a property graph.

Parameters

graph_name: Name of the property graph.

Usage Notes

All the graph tables (VT$, GE$, and so on) will be dropped from the database.

Examples

The following example drops the property graph named mypg.

EXECUTE OPG_APIS.DROP_PG('mypg');

5.21 OPG_APIS.DROP_PG_VIEW

Format

OPG_APIS.DROP_PG_VIEW(
     graph_name  IN VARCHAR2);
     options     IN VARCHAR2);

Description

Drops (deletes) the view definition of a property graph.

Parameters

graph_name: Name of the property graph.
options: (Reserved for future use.)

Usage Notes

Oracle supports creating physical property graphs and property graph views. For example, given an RDF model, it supports creating property graph views over the RDF model, so that you can run property graph analytics on top of the RDF graph.

This procedure cannot be undone.

Examples

The following example drops the view definition of the property graph named mypg.

EXECUTE OPG_APIS.DROP_PG_VIEW('mypg');

5.22 OPG_APIS.DROP_VERTICES_TEXT_IDX

Format

OPG_APIS.DROP_VERTICES_TEXT_IDX(
     graph_owner IN VARCHAR2,
     graph_name  IN VARCHAR2,
     options     IN VARCHAR2 DEFAULT NULL);

Description

Drops a text index on a property graph vertex table.

Parameters

graph_owner: Owner of the property graph.
graph_name: Name of the property graph.
options: Additional settings for the operation.

Usage Notes

A text index must already exist on the property graph vertex table.

Examples

The following example drops the text index on the vertex table of property graph mypg that is owned by user SCOTT.

EXECUTE OPG_APIS.DROP_VERTICES_TEXT_IDX('SCOTT', 'mypg', null);

5.23 OPG_APIS.ESTIMATE_TRIANGLE_RENUM

Format

COUNT_TRIANGLE_ESTIMATE(
   edge_tab_name IN VARCHAR2,
   wt_undBM      IN VARCHAR2,
   wt_rnmap      IN VARCHAR2,
   wt_undAM      IN VARCHAR2,
   num_sub_ptns  IN INTEGER DEFAULT 1,
   chunk_id      IN INTEGER DEFAULT 1,
   dop           IN INTEGER DEFAULT 1,
   tbs           IN VARCHAR2 DEFAULT NULL,
   options       IN VARCHAR2 DEFAULT NULL
   ) RETURN NUMBER;

Description

Estimates the number of triangles in a property graph.

Parameters

edge_tab_name

Name of the property graph edge table.

wt_undBM

A working table holding an undirected version of the original graph (before renumbering optimization).

wt_rnmap

A working table that is a mapping table for renumbering optimization.

wt_undAM

A working table holding the undirected version of the graph data after applying the renumbering optimization.

num_sub_ptns

Number of logical subpartitions used in calculating triangles . Must be a positive integer, power of 2 (1, 2, 4, 8, ...). For a graph with a relatively small maximum degree, use the value 1 (the default).

chunk_id

The logical subpartition to be used in triangle estimation (Only this partition will be counted). It must be an integer between 0 and num_sub_ptns*num_sub_ptns-1.

dop

Degree of parallelism for the operation. The default is 1 (no parallelism).

tbs

Name of the tablespace to hold the data stored in working tables.

options

Additional settings for operation. An optional string with one or more (comma-separated) of the following values:

PDML=T enables parallel DML.

Usage Notes

This function counts the total triangles in a portion of size 1/(num_sub_ptns*num_sub_ptns) of the graph; so to estimate the total number of triangles in the graph, you can multiply the result by num_sub_ptns*num_sub_ptns.

The property graph edge table must exist in the database, and the OPG_APIS.COUNT_TRIANGLE_PREP procedure must already have been executed.

Examples

The following example estimates the number of triangle in the property graph named connections. It does not perform the cleanup after it finishes, so you can count triangles again on the same graph without calling the preparation procedure.

set serveroutput on

DECLARE
  wt1 varchar2(100);  -- intermediate working table
  wt2 varchar2(100);
  wt3 varchar2(100);
  n number;
BEGIN
  opg_apis.count_triangle_prep('connectionsGE$', wt1, wt2, wt3);
  n := opg_apis.estimate_triangle_renum(
     'connectionsGE$',
      wt1,
      wt2,
      wt3,
      num_sub_ptns=>64,
      chunk_id=>2048,
      dop=>2,
      tbs => 'MYPG_TS',
      options=>'PDML=T'
      ); 
  dbms_output.put_line('estimated number of triangles ' || (n * 64 * 64));
END;
/

5.24 OPG_APIS.EXP_EDGE_TAB_STATS

Format

OPG_APIS.EXP_EDGE_TAB_STATS(
     graph_name    IN VARCHAR2,
     stattab       IN VARCHAR2,
     statid        IN VARCHAR2 DEFAULT NULL,
     cascade       IN BOOLEAN DEFAULT TRUE,
     statown       IN VARCHAR2 DEFAULT NULL,
     stat_category IN VARCHAR2 DEFAULT 'OBJECT_STATS');

Description

Retrieves statistics for the edge table of a given property graph and stores them in the user-created statistics table.

Parameters

graph_name: Name of the property graph.
stattab: Name of the statistics table.
statid: Optional identifier to associate with these statistics within stattab.
cascade: If TRUE, column and index statistics are exported.
statown: Schema containing stattab.
stat_category: Specifies what statistics to export, using a comma to separate values. The supported values are 'OBJECT_STATS' (the default: table statistics, column statistics, and index statistics) and ‘SYNOPSES' (auxiliary statistics created when statistics are incrementally maintained).

Usage Notes

(None.)

Examples

The following example creates a statistics table, exports into this table the property graph edge table statistics, and issues a query to count the relevant rows for the newly created statistics.

EXECUTE OPG_APIS.CREATE_STAT_TABLE('mystat',null);

EXECUTE OPG_APIS.EXP_EDGE_TAB_STATS('mypg', 'mystat', 'edge_stats_id_1', true, null, 'OBJECT_STATS');

SELECT count(1) FROM mystat WHERE statid='EDGE_STATS_ID_1';

       153

5.25 OPG_APIS.EXP_VERTEX_TAB_STATS

Format

OPG_APIS.EXP_VERTEX_TAB_STATS(
     graph_name    IN VARCHAR2,
     stattab       IN VARCHAR2,
     statid        IN VARCHAR2 DEFAULT NULL,
     cascade       IN BOOLEAN DEFAULT TRUE,
     statown       IN VARCHAR2 DEFAULT NULL,
     stat_category IN VARCHAR2 DEFAULT 'OBJECT_STATS');

Description

Retrieves statistics for the vertex table of a given property graph and stores them in the user-created statistics table.

Parameters

graph_name: Name of the property graph.
stattab: Name of the statistics table.
statid: Optional identifier to associate with these statistics within stattab.
cascade: If TRUE, column and index statistics are exported.
statown: Schema containing stattab.
stat_category: Specifies what statistics to export, using a comma to separate values. The supported values are 'OBJECT_STATS' (the default: table statistics, column statistics, and index statistics) and ‘SYNOPSES' (auxiliary statistics created when statistics are incrementally maintained).

Usage Notes

(None.)

Examples

The following example creates a statistics table, exports into this table the property graph vertex table statistics, and issues a query to count the relevant rows for the newly created statistics.

EXECUTE OPG_APIS.CREATE_STAT_TABLE('mystat',null);

EXECUTE OPG_APIS.EXP_VERTEX_TAB_STATS('mypg', 'mystat', 'vertex_stats_id_1', true, null, 'OBJECT_STATS');

SELECT count(1) FROM mystat WHERE statid='VERTEX_STATS_ID_1';

       108

5.26 OPG_APIS.FIND_CC_MAPPING_BASED

Format

OPG_APIS.FIND_CC_MAPPING_BASED(
     edge_tab_name IN VARCHAR2,
     wt_clusters   IN OUT VARCHAR2,
     wt_undir      IN OUT VARCHAR2,
     wt_cluas      IN OUT VARCHAR2,
     wt_newas      IN OUT VARCHAR2,
     wt_delta      IN OUT VARCHAR2,
     dop           IN INTEGER DEFAULT 4,
     rounds        IN INTEGER DEFAULT 0,
     tbs           IN VARCHAR2 DEFAULT NULL,
     options       IN VARCHAR2 DEFAULT NULL);

Description

Finds connected components in a property graph. All connected components will be stored in the wt_clusters table. The original graph is treated as undirected.

Parameters

edge_tab_name

Name of the property graph edge table.

wt_clusters

A working table holding the final vertex cluster mappings. This table has two columns (VID NUMBER, CLUSTER_ID NUMBER). Column VID stores the vertex ID values, and column CLUSTER_ID stores the corresponding cluster ID values. Cluster ID values are long integers that can have gaps between them.

If an empty name is specified, a new table will be generated, and its name will be returned.

wt_undir

A working table holding an undirected version of the graph.

wt_cluas

A working table holding current cluster assignments.

wt_newas

A working table holding updated cluster assignments.

wt_delta

A working table holding changes ("delta") in cluster assignments.

dop

Degree of parallelism for the operation. The default is 4.

rounds

Maximum umber of iterations to perform in searching for connected components. The default value of 0 (zero) means that computation will continue until all connected components are found.

tbs

Name of the tablespace to hold the data stored in working tables.

options

Additional settings for the operation.

'PDML=T' enables parallel DML.

Usage Notes

The property graph edge table must exist in the database, and the OPG_APIS.FIND_CLUSTERS_PREP. procedure must already have been executed.

Examples

The following example finds the connected components in a property graph named mypg.

DECLARE
  wtClusters   varchar2(200) := 'mypg_clusters';
  wtUnDir      varchar2(200);
  wtCluas      varchar2(200);
  wtNewas      varchar2(200);
  wtDelta      varchar2(200);
BEGIN
  opg_apis.find_clusters_prep('mypgGE$', wtClusters, wtUnDir,
      wtCluas, wtNewas, wtDelta, '');
  dbms_output.put_line('working tables names ' || wtClusters || ' '
|| wtUnDir || ' ' || wtCluas || ' '  || wtNewas    || ' '
|| wtDelta );

opg_apis.find_cc_mapping_based(''mypgGE$', wtClusters, wtUnDir,
      wtCluas, wtNewas, wtDelta, 8, 0, 'MYTBS', 'PDML=T');

--
-- logic to consume results in wtClusters
-- e.g.: 
-- select /*+ parallel(8) */ count(distinct cluster_id) 
--   from mypg_clusters;

-- cleanup all the working tables
  opg_apis.find_clusters_cleanup('mypgGE$', wtClusters, wtUnDir,
      wtCluas, wtNewas, wtDelta, '');

END;
/

5.27 OPG_APIS.FIND_CLUSTERS_CLEANUP

Format

OPG_APIS.FIND_CLUSTERS_CLEANUP(
     edge_tab_name  IN VARCHAR2,
     wt_clusters    IN OUT VARCHAR2,
     wt_undir       IN OUT VARCHAR2,
     wt_cluas       IN OUT VARCHAR2,
     wt_newas       IN OUT VARCHAR2,
     wt_delta       IN OUT VARCHAR2,
     options        IN VARCHAR2 DEFAULT NULL);

Description

Cleans up after running weakly connected components (WCC) cluster detection.

Parameters

edge_tab_name

Name of the property graph edge table.

wt_clusters

A working table holding the final vertex cluster mappings. This table has two columns (VID NUMBER, CLUSTER_ID NUMBER). Column VID stores the vertex ID values, and column CLUSTER_ID stores the corresponding cluster ID values. Cluster ID values are long integers that can have gaps between them.

If an empty name is specified, a new table will be generated, and its name will be returned.

wt_undir

A working table holding an undirected version of the graph.

wt_cluas

A working table holding current cluster assignments.

wt_newas

A working table holding updated cluster assignments.

wt_delta

A working table holding changes ("delta") in cluster assignments.

options

(Reserved for future use.)

Usage Notes

The property graph edge table must exist in the database.

Examples

The following example cleans up after performing doing cluster detection in a property graph named mypg.

EXECUTE OPG_APIS.FIND_CLUSTERS_CLEANUP('mypgGE$', wtClusters, wtUnDir, wtCluas, wtNewas, wtDelta, null);

5.28 OPG_APIS.FIND_CLUSTERS_PREP

Format

OPG_APIS.FIND_CLUSTERS_PREP(
     edge_tab_name  IN VARCHAR2,
     wt_clusters    IN OUT VARCHAR2,
     wt_undir       IN OUT VARCHAR2,
     wt_cluas       IN OUT VARCHAR2,
     wt_newas       IN OUT VARCHAR2,
     wt_delta       IN OUT VARCHAR2,
     options        IN VARCHAR2 DEFAULT NULL);

Description

Prepares for running weakly connected components (WCC) cluster detection.

Parameters

edge_tab_name

Name of the property graph edge table.

wt_clusters

A working table holding the final vertex cluster mappings. This table has two columns (VID NUMBER, CLUSTER_ID NUMBER). Column VID stores the vertex ID values, and column CLUSTER_ID stores the corresponding cluster ID values. Cluster ID values are long integers that can have gaps between them.

If an empty name is specified, a new table will be generated, and its name will be returned.

wt_undir

A working table holding an undirected version of the graph.

wt_cluas

A working table holding current cluster assignments.

wt_newas

A working table holding updated cluster assignments.

wt_delta

A working table holding changes ("delta") in cluster assignments.

options

Additional settings for index creation.

Usage Notes

The property graph edge table must exist in the database.

Examples

The following example prepares for doing cluster detection in a property graph named mypg.

DECLARE
  wtClusters   varchar2(200);
  wtUnDir      varchar2(200);
  wtCluas      varchar2(200);
  wtNewas      varchar2(200);
  wtDelta      varchar2(200);
BEGIN
  opg_apis.find_clusters_prep('mypgGE$', wtClusters, wtUnDir,
      wtCluas, wtNewas, wtDelta, '');
  dbms_output.put_line('working tables names ' || wtClusters || ' '
|| wtUnDir || ' ' || wtCluas || ' '  || wtNewas    || ' '
|| wtDelta );
END;
/

5.29 OPG_APIS.FIND_SP

Format

OPG_APIS.FIND_SP(
     edge_tab_name  IN VARCHAR2,
     source         IN NUMBER,
     dest           IN NUMBER,
     exp_tab        IN OUT VARCHAR2,
     dop            IN INTEGER,
     stats_freq     IN INTEGER DEFAULT 20000,
     path_output    OUT VARCHAR2,
     weights_output OUT VARCHAR2,
     edge_tab_name  IN VARCHAR2,
     options        IN VARCHAR2 DEFAULT NULL,
     scn            IN NUMBER DEFAULT NULL);

Description

Finds the shortest path between given source vertex and destination vertex in the property graph. It assumes each edge has a numeric weight property. (The actual edge property name is not significant.)

Parameters

edge_tab_name

Name of the property graph edge table.

source

Source (start) vertex ID.

dest

Destination (end) vertex ID.

exp_tab

Name of the expansion table to be used for shortest path calculations.

dop

Degree of parallelism for the operation.

stats_freq

Frequency for collecting statistics on the table.

path_output

The output shortest path. It consists of IDs of vertices on the shortest path, which are separated by the space character.

weights_output

The output shortest path weights. It consists of weights of edges on the shortest path, which are separated by the space character.

options

Additional settings for the operation. An optional string with one or more (comma-separated) of the following values:

CREATE_UNDIRECTED=T
REUSE_UNDIRECTED_TAB=T

scn

SCN for the edge table. It can be null.

Usage Notes

The property graph edge table must exist in the database, and the OPG_APIS.FIND_SP_PREP procedure must have already been called.

Examples

The following example prepares for shortest-path calculation, and then finds the shortest path from vertex 1 to vertex 35 in a property graph named mypg.

set serveroutput on
DECLARE
    w     varchar2(2000);
    wtExp varchar2(2000);
    vPath varchar2(2000);
BEGIN
    opg_apis.find_sp_prep('mypgGE$', wtExp, null);
    opg_apis.find_sp('mypgGE$', 1, 35,  wtExp, 1, 200000,  vPath, w, null, null);
    dbms_output.put_line('Shortest path ' || vPath);
    dbms_output.put_line('Path weights '  || w);
END;
/

The output will be similar to the following. It shows one shortest path starting from vertex 1, to vertex 2, and finally to the destination vertex (35).

Shortest path 1    2 35
Path weights 3 2   1 1

5.30 OPG_APIS.FIND_SP_CLEANUP

Format

OPG_APIS.FIND_SP_CLEANUP(
     edge_tab_name  IN VARCHAR2,
     exp_tab        IN OUT VARCHAR2,
     options        IN VARCHAR2 DEFAULT NULL);

Description

Cleans up after running one or more shortest path calculations.

Parameters

edge_tab_name: Name of the property graph edge table.
exp_tab: Name of the expansion table used for shortest path calculations.
options: (Reserved for future use.)

Usage Notes

There is no need to call this procedure after each OPG_APIS.FIND_SP call. You can run multiple shortest path calculations before calling OPG_APIS.FIND_SP_CLEANUP.

Examples

The following example does cleanup work after doing shortest path calculations in a property graph named mypg.

EXECUTE OPG_APIS.FIND_SP_CLEANUP('mypgGE$', wtExpTab, null);

5.31 OPG_APIS.FIND_SP_PREP

Format

OPG_APIS.FIND_SP_PREP(
     edge_tab_name  IN VARCHAR2,
     exp_tab        IN OUT VARCHAR2,
     options        IN VARCHAR2 DEFAULT NULL);

Description

Prepares for shortest path calculations.

Parameters

edge_tab_name

Name of the property graph edge table.

exp_tab

Name of the expansion table to be used for shortest path calculations. If it is empty, an intermediate working table will be created and the table name will be returned in exp_tab.

options

Additional settings for the operation. An optional string with one or more (comma-separated) of the following values:

CREATE_UNDIRECTED=T
REUSE_UNDIRECTED_TAB=T

Usage Notes

The property graph edge table must exist in the database.

Examples

The following example does preparation work before doing shortest path calculations in a property graph named mypg

set serveroutput on
DECLARE
    wtExp  varchar2(2000); -- name of working table for shortest path calculation
BEGIN
    opg_apis.find_sp_prep('mypgGE$', wtExp, null);
    dbms_output.put_line('Working table name ' || wtExp);
END;
/

The output will be similar to the following. (Your output may be different depending on the SQL session ID.)

Working table name "MYPGGE$$TWFS277"

5.32 OPG_APIS.GET_BUILD_ID

Format

OPG_APIS.GET_BUILD_ID() RETURN VARCHAR2;

Description

Returns the current build ID of the Oracle Spatial and Graph property graph support, in YYYYMMDD format.

Parameters

(None.)

Usage Notes

(None.)

Examples

The following example returns the current build ID of the Oracle Spatial and Graph property graph support.

SQL> SELECT OPG_APIS.GET_BUILD_ID() FROM DUAL;

OPG_APIS.GET_BUILD_ID()
--------------------------------------------------------------------------------
20160606

5.33 OPG_APIS.GET_GEOMETRY_FROM_V_COL

Format

OPG_APIS.GET_GEOMETRY_FROM_V_COL(
     v     IN NVARCHAR2,
     srid  IN NUMBER DEFAULT 8307
) RETURN SDO_GEOMETRY;

Description

Returns an SDO_GEOMETRY object constructed using spatial data and optionally an SRID value.

Parameters

v: A String containing spatial data in serialized form.
srid: SRID (coordinate system identifier) to be used in the resulting SDO_GEOMETRY object. The default value is 8307, the Oracle Spatial SRID for the WGS 84 longitude/latitude coordinate system.

Usage Notes

If there is incorrect syntax or a parsing error, this function returns NULL instead of generating an exception.

Examples

The following examples show point, line, and polygon geometries.

SQL> select opg_apis.get_geometry_from_v_col('10.0 5.0',8307) from dual;

OPG_APIS.GET_GEOMETRY_FROM_V_COL('10.05.0',8307)(SDO_GTYPE, SDO_SRID, SDO_POINT(
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(10, 5, NULL), NULL, NULL)


SQL>   select opg_apis.get_geometry_from_v_col('LINESTRING(30 10, 10 30, 40 40)',8307) from dual;

OPG_APIS.GET_GEOMETRY_FROM_V_COL('LINESTRING(3010,1030,4040)',8307)(SDO_GTYPE, S
--------------------------------------------------------------------------------
SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
30, 10, 10, 30, 40, 40))


SQL>  select opg_apis.get_geometry_from_v_col('POLYGON((-83.6  34.1, -83.6 34.3, -83.4 34.3, -83.4 34.1, -83.6 34.1))', 8307) from dual;

OPG_APIS.GET_GEOMETRY_FROM_V_COL('POLYGON((-83.634.1,-83.634.3,-83.434.3,-83.434
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(-83.6, 34.1, -83.6, 34.3, -83.4, 34.3, -83.4, 34.1, -83.6, 34.1))

5.34 OPG_APIS.GET_GEOMETRY_FROM_V_T_COLS

Format

OPG_APIS.GET_GEOMETRY_FROM_V_T_COLS(
     v     IN NVARCHAR2,
     t     IN INTEGER,
     srid  IN NUMBER DEFAULT 8307
) RETURN SDO_GEOMETRY;

Description

Returns an SDO_GEOMETRY object constructed using spatial data, a type value, and optionally an SRID value.

Parameters

v: A String containing spatial data in serialized form,
t: Value indicating the type of value represented by the v parameter. Must be 20. (A null value or any other value besides 20 returns a null SDO_GEOMETRY object.)
srid: SRID (coordinate system identifier) to be used in the resulting SDO_GEOMETRY object. The default value is 8307, the Oracle Spatial SRID for the WGS 84 longitude/latitude coordinate system.

Usage Notes

If there is incorrect syntax or a parsing error, this function returns NULL instead of generating an exception.

Examples

The following examples show point, line, and polygon geometries.

SQL> select opg_apis.get_geometry_from_v_t_cols('10.0 5.0', 20, 8307) from dual;

OPG_APIS.GET_GEOMETRY_FROM_V_T_COLS('10.05.0',20,8307)(SDO_GTYPE, SDO_SRID, SDO_
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(10, 5, NULL), NULL, NULL)


SQL> select opg_apis.get_geometry_from_v_t_cols('LINESTRING(30 10, 10 30, 40 40)', 20, 8307) from dual;

OPG_APIS.GET_GEOMETRY_FROM_V_T_COLS('LINESTRING(3010,1030,4040)',20,8307)(SDO_GT
--------------------------------------------------------------------------------
SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
30, 10, 10, 30, 40, 40))


SQL>  select opg_apis.get_geometry_from_v_t_cols('POLYGON((-83.6  34.1, -83.6 34.3, -83.4 34.3, -83.4 34.1, -83.6 34.1))', 20, 8307) from dual;

OPG_APIS.GET_GEOMETRY_FROM_V_T_COLS('POLYGON((-83.634.1,-83.634.3,-83.434.3,-83.
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(-83.6, 34.1, -83.6, 34.3, -83.4, 34.3, -83.4, 34.1, -83.6, 34.1))

5.35 OPG_APIS.GET_LATLONG_FROM_V_COL

Format

OPG_APIS.GET_LATLONG_FROM_V_COL(
     v     IN NVARCHAR2,
     srid  IN NUMBER DEFAULT 8307
) RETURN SDO_GEOMETRY;

Description

Returns an SDO_GEOMETRY object constructed using spatial data and optionally an SRID value.

Parameters

v: A String containing spatial data in serialized form.
srid: SRID (coordinate system identifier) to be used in the resulting SDO_GEOMETRY object. The default value is 8307, the Oracle Spatial SRID for the WGS 84 longitude/latitude coordinate system.

Usage Notes

This function assumes that for each vertex in the geometry in the v parameter, the first number is the latitude value and the second number is the longitude value. (This is the reverse of the order in an SDO_GEOMETRY object definition, where longitude is first and latitude is second).

If there is incorrect syntax or a parsing error, this function returns NULL instead of generating an exception.

Examples

The following example returns a point SDO_GEOMETRY object. Notice that the coordinate values of the input point are “swapped” in the returned SDO_GEOMETRY object.

SQL> select opg_apis.get_latlong_from_v_col('5.1 10.0', 8307) from dual;

OPG_APIS.GET_LATLONG_FROM_V_COL('5.110.0',8307)(SDO_GTYPE, SDO_SRID, SDO_POINT(X
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(10, 5.1, NULL), NULL, NULL)

5.36 OPG_APIS.GET_LATLONG_FROM_V_T_COLS

Format

OPG_APIS.GET_LATLONG_FROM_V_T_COLS(
     v     IN NVARCHAR2,
     t     IN INTEGER,
     srid  IN NUMBER DEFAULT 8307
) RETURN SDO_GEOMETRY;

Description

Returns an SDO_GEOMETRY object constructed using spatial data, a type value, and optionally an SRID value.

Parameters

v: A String containing spatial data in serialized form.
t: Value indicating the type of value represented by the v parameter. Must be 20. (A null value or any other value besides 20 returns a null SDO_GEOMETRY object.)
srid: SRID (coordinate system identifier) to be used in the resulting SDO_GEOMETRY object. The default value is 8307, the Oracle Spatial SRID for the WGS 84 longitude/latitude coordinate system.

Usage Notes

This function assumes that for each vertex in the geometry in the v parameter, the first number is the latitude value and the second number is the longitude value. (This is the reverse of the order in an SDO_GEOMETRY object definition, where longitude is first and latitude is second).

If there is incorrect syntax or a parsing error, this function returns NULL instead of generating an exception.

Examples

The following example returns a point SDO_GEOMETRY object. Notice that the coordinate values of the input point are “swapped” in the returned SDO_GEOMETRY object.

SQL> select opg_apis.get_latlong_from_v_t_cols('5.1 10.0',20,8307) from dual;

OPG_APIS.GET_LATLONG_FROM_V_T_COLS('5.110.0',20,8307)(SDO_GTYPE, SDO_SRID, SDO_P
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(10, 5.1, NULL), NULL, NULL)

5.37 OPG_APIS.GET_LONG_LAT_GEOMETRY

Format

OPG_APIS.GET_LONG_LAT_GEOMETRY(
     x     IN NUMBER,
     y     IN NUMBER,
     srid  IN NUMBER DEFAULT 8307
) RETURN SDO_GEOMETRY;

Description

Returns an SDO_GEOMETRY object constructed using X and Y point coordinate values, and optionally an SRID value.

Parameters

x: The X (first coordinate) value in the SDO_POINT_TYPE element of the geometry definition.
y: The Y (second coordinate) value in the SDO_POINT_TYPE element of the geometry definition.
srid: SRID (coordinate system identifier) to be used in the resulting SDO_GEOMETRY object. The default value is 8307, the Oracle Spatial SRID for the WGS 84 longitude/latitude coordinate system.

Usage Notes

If there is incorrect syntax or a parsing error, this function returns NULL instead of generating an exception.

Examples

The following example returns the geometry object for a point with X, Y coordinates 10.5, 5.0, and it uses 8307 as the SRID in the resulting geometry object.

SQL> select opg_apis.get_long_lat_geometry(10.0, 5.0, 8307) from dual;

OPG_APIS.GET_LONG_LAT_GEOMETRY(10.0,5.0,8307)(SDO_GTYPE, SDO_SRID, SDO_POINT(X,
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(10, 5, NULL), NULL, NULL)

5.38 OPG_APIS.GET_LATLONG_FROM_V_COL

Format

OPG_APIS.GET_LATLONG_FROM_V_COL(
     v     IN NVARCHAR2,
     srid  IN NUMBER DEFAULT 8307
) RETURN SDO_GEOMETRY;

Description

Returns an SDO_GEOMETRY object constructed using spatial data and optionally an SRID value.

Parameters

v: A String containing spatial data in serialized form.
srid: SRID (coordinate system identifier) to be used in the resulting SDO_GEOMETRY object. The default value is 8307, the Oracle Spatial SRID for the WGS 84 longitude/latitude coordinate system.

Usage Notes

This function assumes that for each vertex in the geometry in the v parameter, the first number is the latitude value and the second number is the longitude value. (This is the reverse of the order in an SDO_GEOMETRY object definition, where longitude is first and latitude is second).

If there is incorrect syntax or a parsing error, this function returns NULL instead of generating an exception.

Examples

The following example returns a point SDO_GEOMETRY object. Notice that the coordinate values of the input point are “swapped” in the returned SDO_GEOMETRY object.

SQL> select opg_apis.get_latlong_from_v_col('5.1 10.0', 8307) from dual;

OPG_APIS.GET_LATLONG_FROM_V_COL('5.110.0',8307)(SDO_GTYPE, SDO_SRID, SDO_POINT(X
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(10, 5.1, NULL), NULL, NULL)

5.39 OPG_APIS.GET_LONGLAT_FROM_V_T_COLS

Format

OPG_APIS.GET_LONGLAT_FROM_V_T_COLS(
     v     IN NVARCHAR2,
     t     IN INTEGER,
     srid  IN NUMBER DEFAULT 8307
) RETURN SDO_GEOMETRY;

Description

Returns an SDO_GEOMETRY object constructed using spatial data, a type value, and optionally an SRID value.

Parameters

v: A String containing spatial data in serialized form.
t: Value indicating the type of value represented by the v parameter. Must be 20. (A null value or any other value besides 20 returns a null SDO_GEOMETRY object.)
srid: SRID (coordinate system identifier) to be used in the resulting SDO_GEOMETRY object. The default value is 8307, the Oracle Spatial SRID for the WGS 84 longitude/latitude coordinate system.

Usage Notes

If there is incorrect syntax or a parsing error, this function returns NULL instead of generating an exception.

Examples

This function assumes that for each vertex in the geometry in the v parameter, the first number is the longitude value and the second number is the latitude value (which is the order in an SDO_GEOMETRY object definition).

The following example returns a point SDO_GEOMETRY object.

SQL> select opg_apis.get_longlat_from_v_t_cols('5.1 10.0',20,8307) from dual;

OPG_APIS.GET_LATLONG_FROM_V_T_COLS('5.110.0',20,8307)(SDO_GTYPE, SDO_SRID, SDO_P
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(5.1, 10, NULL), NULL, NULL)

5.40 OPG_APIS.GET_SCN

Format

OPG_APIS.GET_SCN() RETURN NUMBER;

Description

Returns the SCN (system change number) of the Oracle Spatial and Graph property graph support, in YYYYMMDD format.

Parameters

(None.)

Usage Notes

The SCN value is incremented after each commit.

Examples

The following example returns the current build ID of the Oracle Spatial and Graph property graph support.

SQL> SELECT OPG_APIS.GET_SCN() FROM DUAL;

OPG_APIS.GET_SCN()
------------------
           1478701

5.41 OPG_APIS.GET_VERSION

Format

OPG_APIS.GET_VERSION() RETURN VARCHAR2;

Description

Returns the current version of the Oracle Spatial and Graph property graph support.

Parameters

(None.)

Usage Notes

(None.)

Examples

The following example returns the current version of the Oracle Spatial and Graph property graph support.

SQL> SELECT OPG_APIS.GET_VERSION() FROM DUAL;

OPG_APIS.GET_VERSION()
--------------------------------------------------------------------------------
12.2.0.1 P1

5.42 OPG_APIS.GET_WKTGEOMETRY_FROM_V_COL

Format

OPG_APIS.GET_WKTGEOMETRY_FROM_V_COL(
     v     IN NVARCHAR2,
     srid  IN NUMBER DEFAULT NULL
) RETURN SDO_GEOMETRY;

Description

Returns an SDO_GEOMETRY object based on a geometry in WKT (well known text) form and optionally an SRID.

Parameters

v: A String containing spatial data in serialized form.
srid: SRID (coordinate system identifier) to be used in the resulting SDO_GEOMETRY object. The default value is 8307, the Oracle Spatial SRID for the WGS 84 longitude/latitude coordinate system.

Usage Notes

If there is incorrect syntax or a parsing error, this function returns NULL instead of generating an exception.

Examples

The following statements return a point geometry and a line string geometry

SQL> select opg_apis.get_wktgeometry_from_v_col('POINT(10.0 5.1)', 8307) from dual;

OPG_APIS.GET_WKTGEOMETRY_FROM_V_COL('POINT(10.05.1)',8307)(SDO_GTYPE, SDO_SRID,
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(10, 5.1, NULL), NULL, NULL)


SQL> select opg_apis.get_wktgeometry_from_v_col('LINESTRING(30 10, 10 30, 40 40)',8307) from dual;

OPG_APIS.GET_WKTGEOMETRY_FROM_V_COL('LINESTRING(3010,1030,4040)',8307)(SDO_GTYPE
--------------------------------------------------------------------------------
SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
30, 10, 10, 30, 40, 40))

5.43 OPG_APIS.GET_WKTGEOMETRY_FROM_V_T_COLS

Format

OPG_APIS.GET_WKTGEOMETRY_FROM_V_T_COLS(
     v     IN NVARCHAR2,
     t     IN INTEGER,
     srid  IN NUMBER DEFAULT NULL
) RETURN SDO_GEOMETRY;

Description

Returns an SDO_GEOMETRY object based on a geometry in WKT (well known text) form, a type value, and optionally an SRID.

Parameters

v: A String containing spatial data in serialized form.
t: Value indicating the type of value represented by the v parameter. Must be 20. (A null value or any other value besides 20 returns a null SDO_GEOMETRY object.)
srid: SRID (coordinate system identifier) to be used in the resulting SDO_GEOMETRY object. The default value is 8307, the Oracle Spatial SRID for the WGS 84 longitude/latitude coordinate system.

Usage Notes

If there is incorrect syntax or a parsing error, this function returns NULL instead of generating an exception.

Examples

The following statements return a point geometry and a polygon geometry

SQL> select opg_apis.get_wktgeometry_from_v_t_cols('POINT(10.0 5.1)',20,8307) from dual;

OPG_APIS.GET_WKTGEOMETRY_FROM_V_T_COLS('POINT(10.05.1)',20,8307)(SDO_GTYPE, SDO_
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(10, 5.1, NULL), NULL, NULL)


SQL> select opg_apis.get_wktgeometry_from_v_t_cols('POLYGON((-83.6  34.1, -83.6 34.3, -83.4 34.3, -83.4 34.1, -83.6 34.1))',20,8307) from dual;

OPG_APIS.GET_WKTGEOMETRY_FROM_V_T_COLS('POLYGON((-83.634.1,-83.634.3,-83.434.3,-
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(-83.6, 34.1, -83.6, 34.3, -83.4, 34.3, -83.4, 34.1, -83.6, 34.1))

5.44 OPG_APIS.GRANT_ACCESS

Format

OPG_APIS.GRANT_ACCESS(
     graph_owner IN VARCHAR2,
     graph_name  IN VARCHAR2,
     other_user  IN VARCHAR2,
     privilege   IN VARCHAR2);

Description

Grants access privileges on a property graph to another database user.

Parameters

graph_owner

Owner of the property graph.

graph_name

Name of the property graph.

other_user

Name of the database user to which on e or more access privileges will be granted.

privilege

A string of characters indicating privileges: R for read, S for select, U for update, D for delete, I for insert, A for all. Do not use commas or any other delimiter.

If you specify A, do not specify any other values because A includes all access privileges.

Usage Notes

(None.)

Examples

The following example grants read and select (RS) privileges on the mypg property graph owned by database user SCOTT to database user PGUSR. It then connects as PGUSR and queries the mypg vertex table in the SCOTT schema.

CONNECT scott/<password>

EXECUTE OPG_APIS.GRANT_ACCESS('scott', 'mypg', 'pgusr', 'RS');

CONNECT pgusr/<password>

SELECT count(1) from scott.mypgVT$;

    17

5.45 OPG_APIS.IMP_EDGE_TAB_STATS

Format

OPG_APIS.IMP_EDGE_TAB_STATS(
     graph_name    IN VARCHAR2,
     stattab       IN VARCHAR2,
     statid        IN VARCHAR2 DEFAULT NULL,
     cascade       IN BOOLEAN DEFAULT TRUE,
     statown       IN VARCHAR2 DEFAULT NULL,
     no_invalidate  BOOLEAN DEFAULT FALSE,  
     force              BOOLEAN DEFAULT FALSE,
     stat_category IN VARCHAR2 DEFAULT 'OBJECT_STATS');

Description

Retrieves statistics for the given property graph edge table (GE$) from the user statistics table identified by stattab and stores them in the dictionary. If cascade is TRUE, all index statistics associated with the specified table are also imported.

Parameters

graph_name: Name of the property graph.
stattab: Name of the statistics table.
statid: Optional identifier to associate with these statistics within stattab.
cascade: If TRUE, column and index statistics are exported.
statown: Schema containing stattab.
no_invalidate: If TRUE, does not invalidate the dependent cursors. If FALSE, invalidates the dependent cursors immediately. If DBMS_STATS.AUTO_INVALIDATE (the usual default) is in effect, Oracle Database decides when to invalidate dependent cursors.
force: If TRUE, performs the operation even if the statistics are locked.
stat_category: Specifies what statistics to export, using a comma to separate values. The supported values are 'OBJECT_STATS' (the default: table statistics, column statistics, and index statistics) and ‘SYNOPSES' (auxiliary statistics created when statistics are incrementally maintained).

Usage Notes

(None.)

Examples

The following example creates a statistics table, exports into this table the edge table statistics, issues a query to count the relevant rows for the newly created statistics, and finally imports the statistics back.

EXECUTE OPG_APIS.CREATE_STAT_TABLE('mystat',null);

EXECUTE OPG_APIS.EXP_EDGE_TAB_STATS('mypg', 'mystat', 'edge_stats_id_1', true, null, 'OBJECT_STATS');

SELECT count(1) FROM mystat WHERE statid='EDGE_STATS_ID_1';

       153

EXECUTE OPG_APIS.IMP_EDGE_TAB_STATS('mypg', 'mystat', 'edge_stats_id_1', true, null, false, true, 'OBJECT_STATS');

5.46 OPG_APIS.IMP_VERTEX_TAB_STATS

Format

OPG_APIS.IMP_VERTEX_TAB_STATS(
     graph_name    IN VARCHAR2,
     stattab       IN VARCHAR2,
     statid        IN VARCHAR2 DEFAULT NULL,
     cascade       IN BOOLEAN DEFAULT TRUE,
     statown       IN VARCHAR2 DEFAULT NULL,
     no_invalidate  BOOLEAN DEFAULT FALSE,  
     force              BOOLEAN DEFAULT FALSE,
     stat_category IN VARCHAR2 DEFAULT 'OBJECT_STATS');

Description

Retrieves statistics for the given property graph vertex table (VT$) from the user statistics table identified by stattab and stores them in the dictionary. If cascade is TRUE, all index statistics associated with the specified table are also imported.

Parameters

graph_name: Name of the property graph.
stattab: Name of the statistics table.
statid: Optional identifier to associate with these statistics within stattab.
cascade: If TRUE, column and index statistics are exported.
statown: Schema containing stattab.
no_invalidate: If TRUE, does not invalidate the dependent cursors. If FALSE, invalidates the dependent cursors immediately. If DBMS_STATS.AUTO_INVALIDATE (the usual default) is in effect, Oracle Database decides when to invalidate dependent cursors.
force: If TRUE, performs the operation even if the statistics are locked.
stat_category: Specifies what statistics to export, using a comma to separate values. The supported values are 'OBJECT_STATS' (the default: table statistics, column statistics, and index statistics) and ‘SYNOPSES' (auxiliary statistics created when statistics are incrementally maintained).

Usage Notes

(None.)

Examples

The following example creates a statistics table, exports into this table the vertex table statistics, issues a query to count the relevant rows for the newly created statistics, and finally imports the statistics back.

EXECUTE OPG_APIS.CREATE_STAT_TABLE('mystat',null);

EXECUTE OPG_APIS.EXP_VERTEX_TAB_STATS('mypg', 'mystat', 'vertex_stats_id_1', true, null, 'OBJECT_STATS');

SELECT count(1) FROM mystat WHERE statid='VERTEX_STATS_ID_1';

       108

EXECUTE OPG_APIS.IMP_VERTEX_TAB_STATS('mypg', 'mystat', 'vertex_stats_id_1', true, null, false, true, 'OBJECT_STATS');

5.47 OPG_APIS.PR

Format

OPG_APIS.PR(
     edge_tab_name   IN VARCHAR2,
     d               IN NUMBER DEFAULT 0.85,
     num_iterations  IN NUMBER DEFAULT 10,
     convergence     IN NUMBER DEFAULT 0.1,
     dop             IN INTEGER DEFAULT 4,
     wt_node_pr      IN OUT VARCHAR2,
     wt_node_nextpr  IN OUT VARCHAR2,
     wt_edge_tab_deg IN OUT VARCHAR2,
     wt_delta        IN OUT VARCHAR2,
     tablespace      IN VARCHAR2 DEFAULT NULL,
     options         IN VARCHAR2 DEFAULT NULL,
     num_vertices    OUT NUMBER);

Description

Prepares for page rank calculations.

Parameters

edge_tab_name

Name of the property graph edge table.

d

Damping factor.

num_iterations

Number of iterations for calculating the page rank values.

convergence

A threshold. If the difference between the page rank value of the current iteration and next iteration is lower than this threshold, then computation stops.

dop

Degree od parallelism for the operation.

wt_node_pr

Name of the working table to hold the page rank values of the vertices.

wt_node_pr

Name of the working table to hold the page rank values of the vertices.

wt_node_next_pr

Name of the working table to hold the page rank values of the vertices in the next iteration.

wt_edge_tab_deg

Name of the working table to hold edges and node degree information.

wt_delta

Name of the working table to hold information about some special vertices.

tablespace

Name of the tablespace to hold all the graph data and index data.

options

Additional settings for the operation. An optional string with one or more (comma-separated) of the following values:

CREATE_UNDIRECTED=T
REUSE_UNDIRECTED_TAB=T

num_vertices

Number of vertices processed by the page rank calculation.

Usage Notes

The property graph edge table must exist in the database, and the OPG_APIS.PR_PREP procedure must have been called.

Examples

The following example performs preparation, and then calculates the page rank value of vertices in a property graph named mypg.

set serveroutput on
DECLARE
    wt_pr  varchar2(2000); -- name of the table to hold PR value of the current iteration
    wt_npr varchar2(2000); -- name of the table to hold PR value for the next iteration
    wt3    varchar2(2000); 
    wt4    varchar2(2000); 
    wt5    varchar2(2000); 
    n_vertices number;
BEGIN
    wt_pr := 'mypgPR';
    opg_apis.pr_prep('mypgGE$', wt_pr, wt_npr, wt3, wt4, null);
    dbms_output.put_line('Working table names  ' || wt_pr 
       || ', wt_npr ' || wt_npr || ', wt3 ' || wt3 || ', wt4 '|| wt4);
    opg_apis.pr('mypgGE$', 0.85, 10, 0.01, 4, wt_pr, wt_npr, wt3, wt4, 'SYSAUX', null, n_vertices)
;
END;
/

The output will be similar to the following.

Working table names  "MYPGPR", wt_npr "MYPGGE$$TWPRX277", wt3
"MYPGGE$$TWPRE277", wt4 "MYPGGE$$TWPRD277"

The calculated page rank value is stored in the mypgpr table which has the following definition and data.

SQL> desc mypgpr;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 NODE					   NOT NULL NUMBER
 PR						            NUMBER
 C						              NUMBER

SQL> select node, pr from mypgpr;

      NODE	   PR
---------- ----------
       101	.1925
       201	.2775
       102	.1925
       104  .74383125
       105  .313625
       103	.1925
       100	.15
       200	.15

5.48 OPG_APIS.PR_CLEANUP

Format

OPG_APIS.PR_CLEANUP(
     edge_tab_name   IN VARCHAR2,
     wt_node_pr      IN OUT VARCHAR2,
     wt_node_nextpr  IN OUT VARCHAR2,
     wt_edge_tab_deg IN OUT VARCHAR2,
     wt_delta        IN OUT VARCHAR2,
     options         IN VARCHAR2 DEFAULT NULL);

Description

Performs cleanup after performing page rank calculations.

Parameters

edge_tab_name

Name of the property graph edge table.

wt_node_pr

Name of the working table to hold the page rank values of the vertices.

wt_node_next_pr

Name of the working table to hold the page rank values of the vertices in the next iteration.

wt_edge_tab_deg

Name of the working table to hold edges and node degree information.

wt_delta

Name of the working table to hold information about some special vertices.

options

Additional settings for the operation. An optional string with one or more (comma-separated) of the following values:

CREATE_UNDIRECTED=T
REUSE_UNDIRECTED_TAB=T

Usage Notes

You do not need to do cleanup after each call to the OPG_APIS.PR procedure. You can run several page rank calculations before calling the OPG_APIS.PR_CLEANUP procedure.

Examples

The following example does the cleanup work after running page rank calculations in a property graph named mypg.

EXECUTE OPG_APIS.PR_CLEANUP('mypgGE$', wt_pr, wt_npr, wt3, wt4, null);

5.49 OPG_APIS.PR_PREP

Format

OPG_APIS.PR_PREP(
     edge_tab_name   IN VARCHAR2,
     wt_node_pr      IN OUT VARCHAR2,
     wt_node_nextpr  IN OUT VARCHAR2,
     wt_edge_tab_deg IN OUT VARCHAR2,
     wt_delta        IN OUT VARCHAR2,
     options         IN VARCHAR2 DEFAULT NULL);

Description

Prepares for page rank calculations.

Parameters

edge_tab_name

Name of the property graph edge table.

wt_node_pr

Name of the working table to hold the page rank values of the vertices.

wt_node_next_pr

Name of the working table to hold the page rank values of the vertices in the next iteration.

wt_edge_tab_deg

Name of the working table to hold edges and node degree information.

wt_delta

Name of the working table to hold information about some special vertices.

options

Additional settings for the operation. An optional string with one or more (comma-separated) of the following values:

CREATE_UNDIRECTED=T
REUSE_UNDIRECTED_TAB=T

Usage Notes

The property graph edge table must exist in the database.

Examples

The following example does the preparation work before running page rank calculations in a property graph named mypg.

set serveroutput on
DECLARE
    wt_pr  varchar2(2000); -- name of the table to hold PR value of the current iteration
    wt_npr varchar2(2000); -- name of the table to hold PR value for the next iteration
    wt3    varchar2(2000); 
    wt4    varchar2(2000);
    wt5    varchar2(2000);
BEGIN
    wt_pr := 'mypgPR';
    opg_apis.pr_prep('mypgGE$', wt_pr, wt_npr, wt3, wt4, null);
    dbms_output.put_line('Working table names  ' || wt_pr 
       || ', wt_npr ' || wt_npr || ', wt3 ' || wt3 || ', wt4 '|| wt4);
END;
/

The output will be similar to the following.

Working table names  "MYPGPR", wt_npr "MYPGGE$$TWPRX277", wt3
"MYPGGE$$TWPRE277", wt4 "MYPGGE$$TWPRD277"

5.50 OPG_APIS.PREPARE_TEXT_INDEX

Format

OPG_APIS.PREPARE_TEXT_INDEX();

Description

Performs preparatory work needed before a text index can be created on any NVARCHAR2 columns.

Parameters

None.

Usage Notes

You must have the ALTER SESSION to run this procedure.

Examples

The following example performs preparatory work needed before a text index can be created on any NVARCHAR2 columns.

EXECUTE OPG_APIS.PREPARE_TEXT_INDEX();

5.51 OPG_APIS.RENAME_PG

Format

OPG_APIS.RENAME_PG(
     graph_name    IN VARCHAR2,
     new_graph_name    IN VARCHAR2);

Description

Renames a property graph.

Parameters

graph_name: Name of the property graph.
new_graph_name: New name for the property graph.

Usage Notes

The graph_name property graph must exist in the database.

Examples

The following example changes the name of a property graph named mypg to mynewpg.

EXECUTE OPG_APIS.RENAME_PG('mypg', 'mynewpg');

5.52 OPG_APIS.SPARSIFY_GRAPH

Format

OPG_APIS.SPARSIFY_GRAPH(
     edge_tab_name IN VARCHAR2,
     threshold     IN NUMBER DEFAULT 0.5,
     min_keep      IN INTEGER DEFAULT 1,
     dop           IN INTEGER DEFAULT 4,
     wt_out_tab    IN OUT VARCHAR2,
     wt_und_tab    IN OUT VARCHAR2,
     wt_hsh_tab    IN OUT VARCHAR2,
     wt_mch_tab    IN OUT VARCHAR2,
     tbs           IN VARCHAR2 DEFAULT NULL,
     options       IN VARCHAR2 DEFAULT NULL);

Description

Performs sparsification (edge trimming) for a property graph edge table.

Parameters

edge_tab_name

Name of the property graph edge table (GE$).

threshold

A numeric value controlling how much sparsification needs to be performed. The lower the value, the more edges will be removed. Some typical values are: 0.1, 0.2, ..., 0.5

min_keep

A positive integer indicating at least how many adjacent edges should be kept for each vertex. A recommended value is 1.

dop

Degree of parallelism for the operation.

wt_out_tab

A working table to hold the output, a sparsified graph.

wt_und_tab

A working table to hold the undirected version of the original graph.

wt_hsh_tab

A working table to hold the min hash values of the graph.

wt_mch_tab

A working table to hold matching count of min hash values.

tbs

A working table to hold the working table data.

options

Additional settings for operation. An optional string with one or more (comma-separated) of the following values:

'INMEMORY=T' is an option for creating the schema tables with an 'inmemory' clause.
'IMC_MC_B=T' creates the schema tables with an INMEMORY MEMCOMPRESS BASIC clause.

Usage Notes

The CREATE TABLE privilege is required to call this procedure.

The sparsification algorithm used is a min hash based local sparsification. See "Local graph sparsification for scalable clustering", Proceedings of the 2011 ACM SIGMOD International Conference on Management of Data: https://cs.uwaterloo.ca/~tozsu/courses/CS848/W15/presentations/ElbagouryPresentation-2.pdf

Sparsification only involves the topology of a graph. None of the properties (K/V) are relevant.

Examples

The following example does the preparation work for the edges table of mypg, prints out the working table names, and runs sparsification. The output, a sparsified graph, is stored in a table named LEAN_PG, which has two columns, SVID and DVID.

SQL> set serveroutput on
DECLARE
  my_lean_pg  varchar2(100) := 'lean_pg'; -- output table
  wt2 varchar2(100);
  wt3 varchar2(100);
  wt4 varchar2(100);
BEGIN
  opg_apis.sparsify_graph_prep('mypgGE$', my_lean_pg, wt2, wt3, wt4, null);
  dbms_output.put_line('wt2 ' || wt2 || ', wt3 ' || wt3 || ', wt4 '|| wt4);

  opg_apis.sparsify_graph('mypgGE$', 0.5, 1, 4, my_lean_pg, wt2, wt3, wt4, 'SEMTS', null);
END;
/ 
  
wt2 "MYPGGE$$TWSPAU275", wt3 "MYPGGE$$TWSPAH275", wt4 "MYPGGE$$TWSPAM275"


SQL> describe lean_pg;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 SVID						    NUMBER
 DVID						    NUMBER

5.53 OPG_APIS.SPARSIFY_GRAPH_CLEANUP

Format

OPG_APIS.SPARSIFY_GRAPH_CLEANUP(
     edge_tab_name IN VARCHAR2,
     wt_out_tab    IN OUT VARCHAR2,
     wt_und_tab    IN OUT VARCHAR2,
     wt_hsh_tab    IN OUT VARCHAR2,
     wt_mch_tab    IN OUT VARCHAR2,
     options       IN VARCHAR2 DEFAULT NULL);

Description

Cleans up after sparsification (edge trimming) for a property graph edge table.

Parameters

edge_tab_name: Name of the property graph edge table (GE$).
wt_out_tab: A working table to hold the output, a sparsified graph.
wt_und_tab: A working table to hold the undirected version of the original graph.
wt_hsh_tab: A working table to hold the min hash values of the graph.
wt_mch_tab: A working table to hold matching count of min hash values.
tbs: A working table to hold the working table data
options: (Reserved for future use.)

Usage Notes

The working tables will be dropped after the operation completes.

Examples

The following example does the preparation work for the edges table of mypg, prints out the working table names, runs sparsification, and then performs cleanup.

SQL> set serveroutput on
DECLARE
  my_lean_pg  varchar2(100) := 'lean_pg';
  wt2 varchar2(100);
  wt3 varchar2(100);
  wt4 varchar2(100);
BEGIN
  opg_apis.sparsify_graph_prep('mypgGE$', my_lean_pg, wt2, wt3, wt4, null);
  dbms_output.put_line('wt2 ' || wt2 || ', wt3 ' || wt3 || ', wt4 '|| wt4);

  opg_apis.sparsify_graph('mypgGE$', 0.5, 1, 4, my_lean_pg, wt2, wt3, wt4, 'SEMTS', null);

  -- Add logic here to consume SVID, DVID in LEAN_PG table
  -- 

  -- cleanup 
  opg_apis.sparsify_graph_cleanup('mypgGE$', my_lean_pg, wt2, wt3, wt4, null);
END;
/

5.54 OPG_APIS.SPARSIFY_GRAPH_PREP

Format

OPG_APIS.SPARSIFY_GRAPH_PREP(
     edge_tab_name IN VARCHAR2,
     wt_out_tab    IN OUT VARCHAR2,
     wt_und_tab    IN OUT VARCHAR2,
     wt_hsh_tab    IN OUT VARCHAR2,
     wt_mch_tab    IN OUT VARCHAR2,
     options       IN VARCHAR2 DEFAULT NULL);

Description

Prepares working table names that are necessary to run sparsification for a property graph edge table.

Parameters

edge_tab_name

Name of the property graph edge table (GE$).

wt_out_tab

A working table to hold the output, a sparsified graph.

wt_und_tab

A working table to hold the undirected version of the original graph.

wt_hsh_tab

A working table to hold the min hash values of the graph.

wt_mch_tab

A working table to hold the matching count of min hash values.

options

Additional settings for operation. An optional string with one or more (comma-separated) of the following values:

'INMEMORY=T' is an option for creating the schema tables with an 'inmemory' clause.
'IMC_MC_B=T' creates the schema tables with an INMEMORY MEMCOMPRESS BASIC clause.

Usage Notes

The sparsification algorithm used is a min hash based local sparsification. See "Local graph sparsification for scalable clustering", Proceedings of the 2011 ACM SIGMOD International Conference on Management of Data: https://cs.uwaterloo.ca/~tozsu/courses/CS848/W15/presentations/ElbagouryPresentation-2.pdf

Examples

The following example does the preparation work for the edges table of mypg and prints out the working table names.

set serveroutput on

DECLARE
  my_lean_pg  varchar2(100) := 'lean_pg';
  wt2 varchar2(100);
  wt3 varchar2(100);
  wt4 varchar2(100);
BEGIN
  opg_apis.sparsify_graph_prep('mypgGE$', my_lean_pg, wt2, wt3, wt4, null);
  dbms_output.put_line('wt2 ' || wt2 || ', wt3 ' || wt3 || ', wt4 '|| wt4);
END;
/

The output may be similar to the following.

wt2 "MYPGGE$$TWSPAU275", wt3 "MYPGGE$$TWSPAH275", wt4 "MYPGGE$$TWSPAM275"