Some questions are about connections: which airports can I reach from Auckland with one change, which ways lead to London in up to three flights, which airports are the hubs. Joins and recursive queries can answer them, but a graph states them far more naturally. Oracle AI Database 26ai implements SQL/PGQ, the property graph queries of the SQL standard: you define a graph over your existing tables, and query it with patterns of vertices and edges.
Code for This Guide
The main examples are in the examples/property-graphs folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Define the Graph
A property graph has vertex tables, whose rows are vertices, and edge tables, whose rows are edges between two vertices. Each has a KEY, a LABEL, and the PROPERTIES that queries can use. An edge table names the SOURCE KEY and DESTINATION KEY columns that reference its vertex tables. No data is copied: the graph is a definition over the tables.
Syntax:
create property graph name
vertex tables (
table [key (columns)] [label name] [properties (columns) | properties all columns] [, ...])
[edge tables (
table [key (columns)]
source key (columns) references vertex_table (columns)
destination key (columns) references vertex_table (columns)
[label name] [properties (...)] [, ...])]Nimbus Air's network has airports as vertices and routes as directed edges, from origin to destination.
Example:
create property graph nimbus_network
vertex tables (
airports key (airport_code)
label airport properties (airport_code, city, country_code, is_hub)
)
edge tables (
routes key (route_id)
source key (origin) references airports (airport_code)
destination key (destination) references airports (airport_code)
label route properties (distance_km, block_minutes)
);
select graph_name, graph_mode from user_property_graphs;Output:
Property GRAPH created. GRAPH_NAME GRAPH_MODE _________________ _____________ NIMBUS_NETWORK TRUSTED
The graph is a schema object of type PROPERTY GRAPH. DROP PROPERTY GRAPH removes it, not the tables.
Example:
select object_name, object_type from user_objects where object_type = 'PROPERTY GRAPH';
Output:
OBJECT_NAME OBJECT_TYPE _________________ _________________ NIMBUS_NETWORK PROPERTY GRAPH
Match a Pattern with GRAPH_TABLE
The GRAPH_TABLE operator returns a table of the matches of a path pattern.
Syntax:
graph_table (graph match (v1 [is label] [where ...]) -[e [is label]]-> (v2 ...) [, ...] [where condition] columns (expr as alias [, ...]))
In a pattern, (v IS label) is a vertex and -[e IS label]-> an edge in the direction of the arrow. A WHERE inside an element filters that element, and a WHERE after the pattern filters the whole match. COLUMNS lists what each match returns. This finds the airports one flight away from Auckland.
Example:
select *
from graph_table (nimbus_network
match (a is airport where a.airport_code = 'AKL') -[r is route]-> (b is airport)
columns (b.airport_code as destination, b.city, r.distance_km)
)
order by distance_km;Output:
DESTINATION CITY DISTANCE_KM ______________ _________ ______________ SYD Sydney 2159 DXB Dubai 14200
Follow Several Edges
A longer pattern chains edges. This finds the ways from Auckland to London with exactly one change, and adds up the distances of the two legs.
Example:
-- from Auckland to London with exactly one change
select *
from graph_table (nimbus_network
match (a is airport) -[r1 is route]-> (via is airport)
-[r2 is route]-> (b is airport)
where a.airport_code = 'AKL' and b.airport_code = 'LHR'
columns (via.airport_code as change_at,
r1.distance_km + r2.distance_km as total_km)
)
order by total_km;Output:
CHANGE_AT TOTAL_KM ____________ ___________ DXB 19697
Match Either Direction
An edge without an arrowhead, -[e]-, matches in either direction. Here it finds the routes to and from Singapore.
Example:
-- airports connected to Singapore in either direction, and how many routes link them
select neighbour, count(*) as routes
from graph_table (nimbus_network
match (a is airport where a.airport_code = 'SIN') -[r is route]- (b is airport)
columns (b.airport_code as neighbour)
)
group by neighbour
order by neighbour;Output:
NEIGHBOUR ROUTES ____________ _________ DXB 2 NRT 2 SYD 2
Each neighbor counts twice because Nimbus Air flies each route in both directions, and each direction is its own edge.
Variable-Length Paths
A quantifier after an edge, such as {1,3}, {2}, + or *, repeats it, so one pattern matches paths of several lengths. A variable inside a repeated element is a group variable: in COLUMNS it can only be aggregated, with COUNT, SUM, LISTAGG, and the other aggregates, over the edges of each path.
Example:
-- from Auckland to London with one to three flights; the path as a list of airports
select *
from graph_table (nimbus_network
match (a is airport where a.airport_code = 'AKL')
-[r is route]->{1,3}
(b is airport where b.airport_code = 'LHR')
columns (listagg(r.distance_km, ' + ') as legs_km,
sum(r.distance_km) as total_km,
count(r.distance_km) as flights)
)
order by total_km
fetch first 4 rows only;Output:
LEGS_KM TOTAL_KM FLIGHTS _______________________ ___________ __________ 14200 + 5497 19697 2 2159 + 12043 + 5497 19699 3 14200 + 11001 + 5540 30741 3
LISTAGG lists the legs of each path, SUM totals them, and COUNT gives the number of flights. The two-flight route through Dubai is the shortest, with the three-flight route through Sydney and Dubai only 2 km longer.
Use the Result Like a Table
Because GRAPH_TABLE returns rows, ordinary SQL can group, sort, and join them. This ranks the airports by the number of routes out.
Example:
-- hubs: airports with the most routes out
select airport, count(*) as routes_out
from graph_table (nimbus_network
match (a is airport) -[r is route]-> (b)
columns (a.airport_code as airport)
)
group by airport
order by routes_out desc, airport
fetch first 5 rows only;Output:
AIRPORT ROUTES_OUT __________ _____________ DXB 20 SIN 3 SYD 3 AKL 2 BOM 2
And this joins the destinations from Dubai to COUNTRIES, a table that is not part of the graph, to count them by region.
Example:
-- destinations from Dubai, joined to COUNTRIES and grouped by region
select c.region, count(*) as destinations, round(avg(g.distance_km)) as avg_km
from graph_table (nimbus_network
match (a is airport where a.airport_code = 'DXB') -[r is route]-> (b is airport)
columns (b.country_code, r.distance_km)
) g
join countries c on c.country_code = g.country_code
group by c.region
order by destinations desc, c.region;Output:
REGION DESTINATIONS AVG_KM ________________ _______________ _________ Asia 6 4425 Europe 5 4755 North America 4 11781 Africa 2 4986 Oceania 2 13122 South America 1 12217 6 rows selected.
Related Guides
- How to Rank Graph Paths by Meaning with SQL Property Graphs
- How to Write Recursive Queries with Recursive WITH in Oracle
- How to Query Hierarchies with CONNECT BY in Oracle
Conclusion
A property graph maps existing tables to vertices and edges without copying data. GRAPH_TABLE with MATCH finds patterns of vertices and edges: -[e]-> follows an edge's direction, -[e]- matches either way, and quantifiers such as {1,3} match paths of variable length whose edges you aggregate in COLUMNS. The result is ordinary rows, ready to join, group, and sort.
See my articles more often when you search Google.
