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.
