DBMS_OGA

The DBMS_OGA package provides PL/SQL functions to run graph algorithms on SQL property graphs. These functions are invoked from within the GRAPH_TABLE operator of a SQL graph query.

This chapter contains the following topics:

DBMS_OGA Overview

The DBMS_OGA package allows you to run graph algorithms on SQL property graphs and combine the results with graph pattern matching in SQL graph queries.

Graph algorithms analyze a SQL property graph and can use existing vertex or edge properties. They compute new output properties without changing the graph topology. See Summary of DBMS_OGA Subprograms for the list of supported graph algorithms. User-defined graph algorithms are not supported.

The graph algorithm functions (GAFs) are exposed as PL/SQL functions that take a graph as input and return a new (temporary) graph as output. The returned graph is then used as the first argument of the GRAPH_TABLE operator. Properties computed by GAFs can be referenced like any other properties and combined with pattern matching in SQL graph queries.

However, the computed properties exist only within the scope of that GRAPH_TABLE operator in the SQL graph query. They are not persisted on the original graph, not visible to other sessions or users, and they are finally discarded when the operator (and cursor) completes.

See Also: Running Graph Algorithm Functions in SQL Graph Queries in Oracle AI Database Graph Developer’s Guide for Property Graph

DBMS_OGA Operational Notes

Review the operational notes when working with the DBMS_OGA package.

PL/SQL Types Introduced by Graph Algorithm Functions

Learn about the PL/SQL types introduced by graph algorithm functions (GAFs).

Table: Specific PL/SQL Types in Graph Algorithm Functions

Type Description
ORA_PROPERTY_GRAPH Represents the property graph that is passed as an argument to a GAF.
ORA_VERTEX_INPUT_PROPERTY Represents the vertex property that is passed as input to a GAF. The expected data type for this input property is BINARY_DOUBLE.
ORA_VERTEX_OUTPUT_PROPERTY Represents the output vertex property that stores the results computed by a GAF. Only properties of data types BINARY_DOUBLE and NUMBER are supported.
ORA_EDGE_INPUT_PROPERTY Represents the edge property that is passed as input to a GAF. The expected data type for this input property is BINARY_DOUBLE.
ORA_EDGE_OUTPUT_PROPERTY Represents the output edge property.

Input Properties

If a graph algorithm function (GAF) reads a graph property during computation, you must pass the property as an argument to the algorithm using an ORA_VERTEX_INPUT_PROPERTY or ORA_EDGE_INPUT_PROPERTY input-property type.

Note the following about input properties:

Input Property Types

Input properties are specified using arguments of type ORA_VERTEX_INPUT_PROPERTY or ORA_EDGE_INPUT_PROPERTY.

When an input property is passed to a GAF, its data type needs to be either BINARY_DOUBLE or a numeric data type that is implicitly convertible to BINARY_DOUBLE. Input property data types are compared to and possibly converted to BINARY_DOUBLE as follows:

Also, note that the data type of an input property depends on the MIXED PROPERTY TYPES mode of the graph. See Using Graph Options to Create SQL Property Graphs in Oracle AI Database Graph Developer’s Guide for Property Graph for more information on creating a SQL property graph using MIXED PROPERTY TYPES options.

As a result, in ALLOW MIXED TYPES mode, multiple input property types may be identified. Each identified type is compared to and, if needed, implicitly converted to the expected BINARY_DOUBLE type.

If you expect NULL values in input properties, then note that you can pass a default value to the PROPERTY pseudo-operator. See Using the PROPERTY Pseudo-Operator for more information on default values.

Output Properties

A graph algorithm function (GAF) creates an output property with the identifier (property name) you pass as an argument. This new property contains the results of the algorithm and is accessible in the property graph returned by the graph function.

Note the following about output properties:

Output Property Types

Output properties are represented by arguments of type ORA_VERTEX_OUTPUT_PROPERTY or ORA_EDGE_OUTPUT_PROPERTY.

The algorithm specifies the data types of the output properties. Currently, only properties of types BINARY_DOUBLE and NUMBER can be created through graph algorithms. The output property created for every vertex or edge table has the same data type. This is consistent with graphs that are created in DISALLOW MIXED PROPERTY TYPES mode.

Using the PROPERTY Pseudo-Operator

You can pass the names of the input and output properties to a graph algorithm function (GAF) using the PROPERTY pseudo-operator. These are represented by ORA_VERTEX_INPUT_PROPERTY, ORA_EDGE_INPUT_PROPERTY, ORA_VERTEX_OUTPUT_PROPERTY, or ORA_EDGE_OUTPUT_PROPERTY in the GAF signature.

The syntax for the PROPERTY pseudo-operator is as shown:

PROPERTY(<element_type> <input_or_output> <property_name> <optional_default_value>)

In the preceding pattern:

The following shows an example where the output rank vertex property is specified using the PROPERTY pseudo-operator:

PROPERTY(VERTEX OUTPUT rank)

Also, the property names can be quoted and follow all the rules of SQL quoted identifiers:

DBMS_OGA Security Model

Review the privileges required to run graph algorithm functions.

To run the graph algorithm functions (GAFs) in DBMS_OGA:

When a graph algorithm function is invoked, it may issue recursive SQL statements. These recursive statements execute under the graph owner’s identity, and the graph owner’s privileges are used to parse and run them.

In addition, the graph algorithm function invoker must have sufficient temporary tablespace to execute the function. The amount of temporary tablespace required grows linearly with the total number of rows across all vertex tables in the graph. For BELLMAN_FORD specifically, it grows linearly with the total number of vertices and edges in the graph.

DBMS_OGA Examples

This section provides examples of running graph algorithms on a SQL property graph.

As a prerequisite to run the examples, connect to the database as a schema user and create the following tables with sample data.

CREATE TABLE Cities (
  cid         NUMBER        NOT NULL,
  name        VARCHAR2(100)  NOT NULL,
  population  NUMBER,
  CONSTRAINT pk_cities PRIMARY KEY (cid)
);

CREATE TABLE Residents (
  rid          NUMBER        NOT NULL,
  name         VARCHAR2(50)  NOT NULL,
  age          NUMBER,
  lives_in_cid NUMBER        NOT NULL,
  CONSTRAINT pk_persons PRIMARY KEY (rid),
  CONSTRAINT fk_persons_city FOREIGN KEY (lives_in_cid)
    REFERENCES Cities (cid)
);

CREATE TABLE Knows (
  res1_rid  NUMBER NOT NULL,
  res2_rid  NUMBER NOT NULL,
  CONSTRAINT pk_knows PRIMARY KEY (res1_rid, res2_rid),
  CONSTRAINT fk_knows_p1 FOREIGN KEY (res1_rid) REFERENCES Residents (rid),
  CONSTRAINT fk_knows_p2 FOREIGN KEY (res2_rid) REFERENCES Residents (rid)
);

CREATE TABLE Roads (
  city1_cid  NUMBER NOT NULL,
  city2_cid  NUMBER NOT NULL,
  distance   NUMBER NOT NULL,
  CONSTRAINT pk_roads PRIMARY KEY (city1_cid, city2_cid),
  CONSTRAINT fk_roads_c1 FOREIGN KEY (city1_cid) REFERENCES Cities (cid),
  CONSTRAINT fk_roads_c2 FOREIGN KEY (city2_cid) REFERENCES Cities (cid)
 );

INSERT INTO Cities (cid, name, population) VALUES (101, 'Austin',     980000);
INSERT INTO Cities (cid, name, population) VALUES (102, 'Dallas',    1300000);
INSERT INTO Cities (cid, name, population) VALUES (103, 'Houston',   2300000);
INSERT INTO Cities (cid, name, population) VALUES (104, 'San Antonio',1550000);
INSERT INTO Cities (cid, name, population) VALUES (105, 'El Paso',    680000);

INSERT INTO Residents (rid, name, age, lives_in_cid) VALUES (1, 'Mary',  29, 101);
INSERT INTO Residents (rid, name, age, lives_in_cid) VALUES (2, 'Bob',   35, 102);
INSERT INTO Residents (rid, name, age, lives_in_cid) VALUES (3, 'Tom',   27, 103);
INSERT INTO Residents (rid, name, age, lives_in_cid) VALUES (4, 'Alice', 41, 104);
INSERT INTO Residents (rid, name, age, lives_in_cid) VALUES (5, 'Ben',   33, 105);

INSERT INTO Knows (res1_rid, res2_rid) VALUES (1, 2);
INSERT INTO Knows (res1_rid, res2_rid) VALUES (2, 3);
INSERT INTO Knows (res1_rid, res2_rid) VALUES (3, 4);
INSERT INTO Knows (res1_rid, res2_rid) VALUES (4, 5);
INSERT INTO Knows (res1_rid, res2_rid) VALUES (5, 1);

INSERT INTO Roads (city1_cid, city2_cid, distance) VALUES (101, 102, 195);
INSERT INTO Roads (city1_cid, city2_cid, distance) VALUES (102, 103, 240);
INSERT INTO Roads (city1_cid, city2_cid, distance) VALUES (103, 104, 200);
INSERT INTO Roads (city1_cid, city2_cid, distance) VALUES (104, 105, 550);
INSERT INTO Roads (city1_cid, city2_cid, distance) VALUES (101, 103, 165);

Then, create the addresses property graph using the following CREATE PROPERTY GRAPH statement.

CREATE PROPERTY GRAPH addresses
VERTEX TABLES (
  Residents AS Resident
    PROPERTIES (name, age),
  Cities AS City
    PROPERTIES (name, population)
)
EDGE TABLES (
  Knows
    SOURCE KEY (res1_rid) REFERENCES Resident (rid)
    DESTINATION KEY (res2_rid) REFERENCES Resident (rid)
    NO PROPERTIES,
  Residents AS LivesIn
    SOURCE KEY (rid) REFERENCES Resident (rid)
    DESTINATION KEY (lives_in_cid) REFERENCES City (cid)
    PROPERTIES (0 AS distance),
  Roads AS ConnectedTo
    SOURCE KEY (city1_cid) REFERENCES City (cid)
    DESTINATION KEY (city2_cid) REFERENCES City (cid)
    PROPERTIES (distance)
);

You can then run the examples described in the following sections against the addresses SQL property graph.

Summary of DBMS_OGA Subprograms

This section provides a list of the DBMS_OGA subprograms and briefly describes each one.

Table: DBMS_OGA Subprograms

Subprogram Description
BELLMAN_FORD The Bellman-Ford algorithm computes the shortest-path distances from a start vertex to all other vertices in a weighted directed graph.
PAGERANK The PageRank algorithm computes an importance score for each vertex in the graph.
PERSONALIZED_PAGERANK The Personalized PageRank algorithm is a variant of the classic PageRank. It computes personalized PageRank scores for vertices in a graph, based on a single vertex.
PERSONALIZED_PAGERANK_SET The Personalized PageRank algorithm is a variant of the classic PageRank. It computes personalized PageRank scores for vertices in a graph, based on a vertex set.
WCC The Weakly Connected Components (WCC) algorithm finds connected components in a graph.

BELLMAN_FORD

You can use the DBMS_OGA.BELLMAN_FORD algorithm to find the shortest path distances from a specified source vertex to all other vertices in the graph.

Syntax

DBMS_OGA.BELLMAN_FORD (
  g               IN ORA_PROPERTY_GRAPH,
  start_vertex    IN JSON,
  length_property IN ORA_EDGE_INPUT_PROPERTY,
  dist            IN ORA_VERTEX_OUTPUT_PROPERTY
) RETURN ORA_PROPERTY_GRAPH;

Parameters

Table: BELLMAN_FORD Function Parameters

Parameter Description
g Name of the property graph.
start_vertex Specifies the graph vertex that should be used as the starting point for the Bellman-Ford algorithm.
length_property References the edge property which represents the length of the edges in the graph.
dist Represents the output vertex property name which stores the results of the graph algorithm.

Usage Notes

Example: DBMS_OGA.BELLMAN_FORD

The following example query runs the DBMS_OGA.BELLMAN_FORD algorithm on the addresses graph and returns the computed shortest distance from the City vertex with cid = 101 (Austin) to all other City vertices.

SELECT *
FROM GRAPH_TABLE (
  DBMS_OGA.BELLMAN_FORD(
    addresses,
    JSON('{"GRAPH_OWNER":"GRAPHUSER","GRAPH_NAME":"ADDRESSES","ELEM_TABLE":"CITY","KEY_VALUE":{"CID":101}}'),
    PROPERTY(EDGE INPUT distance DEFAULT ON NULL 0), PROPERTY(VERTEX OUTPUT dist))
  MATCH (c IS city)
  COLUMNS (c.name AS to_city, c.dist)
) ORDER BY dist ASC;

As seen in the preceding code, the query uses each edge’s distance property as the edge weight for the Bellman-Ford algorithm. If an edge’s distance is null, it is treated as zero, as specified by DEFAULT ON NULL 0. The function writes the computed distance from the source to each reachable vertex into a property named dist.

"TO_CITY"    "DIST"
Austin       0.0
Houston      165.0
Dallas       195.0
San Antonio  365.0
El Paso      915.0

PAGERANK

You can use the DBMS_OGA.PAGERANK algorithm to measure the relative importance of a vertex in a graph.

Syntax

DBMS_OGA.PAGERANK (
  g               IN ORA_PROPERTY_GRAPH,
  rank            IN ORA_VERTEX_OUTPUT_PROPERTY,
  max_iter        IN INTEGER,
  tolerance       IN BINARY_DOUBLE,
  damping_factor  IN BINARY_DOUBLE,
  normalize       IN BOOLEAN
) RETURN ORA_PROPERTY_GRAPH;

Parameters

Table: PAGERANK Function Parameters

Parameter Description
g Name of the property graph.
rank Represents the output vertex property name which stores the rank value computed by the graph algorithm.
max_iter Specifies the maximum number of iterations to be performed by the algorithm.
tolerance Specifies the maximum error tolerated between two consecutive iterations.
damping_factor Represents the damping factor for the computation of the rank. A common value is 0.85.
normalize Controls how dangling vertices affect the total rank value (that is, vertices that have no outgoing neighbors).
  • TRUE: Indicates that rank values are normalized taking into account the dangling vertices. This implies that the sum of all the rank values in the graph will always be equal to 1.
  • FALSE: Indicates that the dangling vertices are not included for normalization. This implies that the rank values may end up not adding to 1 over the whole graph, although the relative importance of each vertex will still be given by the rank value.

Usage Notes

Example: DBMS_OGA.PAGERANK

The following example runs the PageRank algorithm on the addresses graph and then projects the name and rank for every CITY in the graph.

SELECT name, rank
FROM GRAPH_TABLE (
  DBMS_OGA.pagerank (
    addresses,
    PROPERTY(VERTEX OUTPUT rank), 10, 1.0, 0.85d, FALSE)
  MATCH (c IS city)
  COLUMNS (c.name, c.rank)
) ORDER BY rank DESC;

The example produces the following output:

"NAME"       "RANK"
Houston      0.18500000000000003
El Paso      0.14250000000000002
San Antonio  0.14250000000000002
Dallas       0.1
Austin       0.05750000000000001

PERSONALIZED_PAGERANK

You can use the DBMS_OGA.PERSONALIZED_PAGERANK algorithm to compute personalized PageRank scores for vertices in a graph, based on a single vertex.

Syntax

DBMS_OGA.PERSONALIZED_PAGERANK (
  g               IN ORA_PROPERTY_GRAPH,
  rank            IN ORA_VERTEX_OUTPUT_PROPERTY,
  input_vertex    IN JSON,
  max_iter        IN INTEGER,
  tolerance       IN BINARY_DOUBLE,
  damping_factor  IN BINARY_DOUBLE,
  normalize       IN BOOLEAN
) RETURN ORA_PROPERTY_GRAPH;

Parameters

Table: PERSONALIZED_PAGERANK Function Parameters

Parameter Description
g Name of the property graph.
rank Represents the output vertex property name which stores the rank value computed by the graph algorithm.
input_vertex Represents the given input vertex used to personalize the PageRank computation.
max_iter Specifies the maximum number of iterations to be performed by the algorithm.
tolerance Specifies the maximum error tolerated between two consecutive iterations.
damping_factor Represents the damping factor for the computation of the rank. A common value is 0.85.
normalize Controls how dangling vertices affect the total rank value (that is, vertices that have no outgoing neighbors).
  • TRUE: Indicates that rank values are normalized taking into account the dangling vertices. This implies that the sum of all the rank values in the graph will always be equal to 1.
  • FALSE: Indicates that the dangling vertices are not included for normalization. This implies that the rank values may end up not adding to 1 over the whole graph, although the relative importance of each vertex will still be given by the rank value.

Usage Notes

Example: DBMS_OGA.PERSONALIZED_PAGERANK

The following example computes the personalized PageRank score on the addresses graph, giving importance to the input RESIDENT vertex with rid = 3. It then projects the name and rank for every RESIDENT vertex in the graph.

SELECT *
FROM GRAPH_TABLE (
  DBMS_OGA.personalized_pagerank(
    addresses,
    PROPERTY(VERTEX OUTPUT rank),
    JSON('{
      "GRAPH_OWNER": "GRAPHUSER",
      "GRAPH_NAME": "ADDRESSES",
      "ELEM_TABLE": "RESIDENT",
      "KEY_VALUE": { "RID": 3 }
    }'),
    10,
    1.0,
    0.85d,
    FALSE
  )
  MATCH (r IS resident)
  COLUMNS (
    r.name,
    r.rank
  )
) ORDER BY rank DESC;

The example produces the following output:

"NAME"  "RANK"
Tom     0.15000000000000002
Alice   0.06375
Bob     0.03262539062499999
Ben     0.02709375
Mary    0.01151484375

PERSONALIZED_PAGERANK_SET

You can use the DBMS_OGA.PERSONALIZED_PAGERANK_SET algorithm to compute personalized PageRank scores for vertices in a graph, based on a set of vertices within a graph.

Syntax

DBMS_OGA.PERSONALIZED_PAGERANK_SET (
  g               IN ORA_PROPERTY_GRAPH,
  rank            IN ORA_VERTEX_OUTPUT_PROPERTY,
  input_vertices  IN JSON,
  max_iter        IN INTEGER,
  tolerance       IN BINARY_DOUBLE,
  damping_factor  IN BINARY_DOUBLE,
  normalize       IN BOOLEAN
) RETURN ORA_PROPERTY_GRAPH;

Parameters

Table: PERSONALIZED_PAGERANK_SET Function Parameters

Parameter Description
g Name of the property graph.
rank Represents the output vertex property name which stores the rank value computed by the graph algorithm.
input_vertices Represents the given input vertex set used to personalize the PageRank computation.
max_iter Specifies the maximum number of iterations to be performed by the algorithm.
tolerance Specifies the maximum error tolerated between two consecutive iterations.
damping_factor Represents the damping factor for the computation of the rank. A common value is 0.85.
normalize Controls how dangling vertices affect the total rank value (that is, vertices that have no outgoing neighbors).
  • TRUE: Indicates that rank values are normalized taking into account the dangling vertices. This implies that the sum of all the rank values in the graph will always be equal to 1.
  • FALSE: Indicates that the dangling vertices are not included for normalization. This implies that the rank values may end up not adding to 1 over the whole graph, although the relative importance of each vertex will still be given by the rank value.

Usage Notes

Example: DBMS_OGA.PERSONALIZED_PAGERANK_SET

The following example computes Personalized PageRank on addresses graph, giving importance to all residents with age > 30. It then projects the name and rank for every RESIDENT vertex in the graph.

The example is executed in two steps as shown:

  1. Construct the set of vertices targeting all residents with age > 30.

    SELECT JSON_ARRAYAGG (p_json)
FROM GRAPH_TABLE(
addresses
MATCH (r IS Resident)
WHERE r.age > 30
COLUMNS(VERTEX_ID(r) AS p_json)
);

    The query produces the following output:

    [{"GRAPH_OWNER":"GRAPHUSER","GRAPH_NAME":"ADDRESSES","ELEM_TABLE":"RESIDENT","KEY_VALUE":{"RID":2}},
 {"GRAPH_OWNER":"GRAPHUSER","GRAPH_NAME":"ADDRESSES","ELEM_TABLE":"RESIDENT","KEY_VALUE":{"RID":4}},
 {"GRAPH_OWNER":"GRAPHUSER","GRAPH_NAME":"ADDRESSES","ELEM_TABLE":"RESIDENT","KEY_VALUE":{"RID":5}}
]

  2. Run the DBMS_OGA.PERSONALIZED_PAGERANK_SET function to compute the personalized PageRank scores biased towards the set of vertices obtained in the previous step.

    SELECT *
FROM GRAPH_TABLE (
  DBMS_OGA.personalized_pagerank_set(
    addresses,
    PROPERTY(VERTEX OUTPUT rank),
    JSON('[
      {
        "GRAPH_OWNER": "GRAPHUSER",
        "GRAPH_NAME": "ADDRESSES",
        "ELEM_TABLE": "RESIDENT",
        "KEY_VALUE": { "RID": 2 }
      },
      {
        "GRAPH_OWNER": "GRAPHUSER",
        "GRAPH_NAME": "ADDRESSES",
        "ELEM_TABLE": "RESIDENT",
        "KEY_VALUE": { "RID": 4 }
      },
      {
        "GRAPH_OWNER": "GRAPHUSER",
        "GRAPH_NAME": "ADDRESSES",
        "ELEM_TABLE": "RESIDENT",
        "KEY_VALUE": { "RID": 5 }
      }
    ]'),
    50,
    0.1,
    0.85d,
    FALSE
  )
  MATCH (r IS Resident)
  COLUMNS (
    r.name,
    r.rank
  )
) ORDER BY rank DESC;

The query output is as shown:

"NAME"  "RANK"
Ben     0.07607621885986329
Bob     0.06439765696818034
Alice   0.06219064524943034
Mary    0.03304200375773112
Tom     0.027014198840332033

WCC

You can use the DBMS_OGA.WCC algorithm to find the connected components in a graph.

Syntax

DBMS_OGA.WCC (
  g               IN ORA_PROPERTY_GRAPH,
  comp_id         IN ORA_VERTEX_OUTPUT_PROPERTY
) RETURN ORA_PROPERTY_GRAPH;

Parameters

Table: WCC Function Parameters

Parameter Description
g Name of the property graph.
comp_id Represents the output vertex property name which stores the component ID for a vertex as computed by the algorithm.

Usage Notes

The DBMS_OGA.WCC function creates a new vertex property with the name as given to the comp_id argument to store the component ID for a vertex. The data type for this new vertex property will be INTEGER.

If two vertices are assigned the same component ID, then they belong to the same weakly connected component. This means that there exists a path between them in the underlying undirected graph (that is, when edge directions are ignored). If the graph contains more than one component ID value, it means there are non-connected components in the graph (sets of vertices that are not connected to each other).

The component ID values produced by the algorithm are not guaranteed to be the same between two different runs. Also, the component IDs might not start at 0 or 1. If the algorithm discovers multiple connected components, the IDs might not be contiguous. For example, the component IDs can be 2, 5, and 17 instead of 2, 3, and 4.

Example: DBMS_OGA.WCC

The following example uses the DBMS_OGA.WCC function to find the connected components in addresses graph. It then projects the name and component ID for every CITY vertex in the graph.

SELECT name,comp_id
FROM GRAPH_TABLE (
  DBMS_OGA.wcc(
    addresses,
    PROPERTY(VERTEX OUTPUT comp_id)
  )
  MATCH (c IS city)
  COLUMNS (
    c.name    AS name,
    c.comp_id AS comp_id
  )
);

The example produces the following output. All cities have COMP_ID = 2, which indicates they are all connected.

NAME         COMP_ID
Austin       2
Dallas       2
Houston      2
San Antonio  2
El Paso      2