Oracle Database 23c - SQL Property Graphs and SQL/PGQ

Oracle이 새로운 Long Term Support Release로 내놓은 Oracle 23c database는 그야말로 여러 가지 기능에서 대격변이 있었다. 몽고DB를 대체할 수 있을 만큼 document db에 잘 대응되고, SQL로 쉽게 JSON과 XML, 그리고 텍스트 형식의 데이터를 읽고 관리할 수 있다. 게다가 이번 Oracle 23c에는 흥미롭게도, 페이스북 등 유명 SNS 기업에서 많이 채택하는 Graph QL에도 대응되는 기술도 포함되어 있었다. Vertex (정점) 테이블에는 주로 고객과 같은 비교적 고정적인 인적 정보 리스트를 담고, Edge (연결) 테이블에는 connection이나 sales과 같이 사람 간에 일어나는 동적인 관계에 대한 정보를 담는다. 하나의 Vertex 테이블에 여러 Edge 테이블을 연결하여 서로 다른 그래프 테이블을 생성하여 확장할 수 있는 점이 장점이다.

 

 

Free Oracle Database – Quick Start Guide

 

 

 

1. Setup

$ sqlplus sys/oracle as sysdba

alter session set container=freepdb1;
create user ryu identified by oracle;
grant dba to ryu;
conn ryu/oracle;

 

 

2. Create Vertex and Edge tables

/* Vertex table */
CREATE TABLE PEOPLE (
	person_id	NUMBER PRIMARY KEY,
    name		VARCHAR2(15)
 );
   
 INSERT INTO PEOPLE (person_id, name)
 VALUES 
 (1, 'Wonder Woman'),
 (2, 'Peter Parker'),
 (3, 'Jean Grey'),
 (4, 'Clark Kent'),
 (5, 'Bruce Banner');
 COMMIT;

 

/* Edge table */
CREATE TABLE CONNECTIONS (
	connection_id	NUMBER PRIMARY KEY,
    person_id_1		NUMBER,
    person_id_2		NUMBER,
    CONSTRAINT connections_people_1_fk FOREIGN KEY (person_id_1) REFERENCES people (person_id),
    CONSTRAINT connections_people_2_fk FOREIGN KEY (person_id_2) REFERENCES people (person_id)
);

CREATE INDEX CONNECTIONS_PERSON_1_IDX ON connections(person_id_1);
CREATE INDEX CONNECTIONS_PERSON_2_IDX ON connections(person_id_2);

INSERT INTO CONNECTIONS (connection_id, person_id_1, person_id_2)
VALUES
(1,  1, 2),
(2,  1, 3),
(3,  1, 4),
(4,  2, 4),
(5,  3, 1),
(6,  3, 4),
(7,  3, 5),
(8,  4, 1),
(9,  5, 1),
(10, 5, 2),
(11, 5, 3);
COMMIT;

EXEC dbms_stats.gather_table_stats(null, 'people');
EXEC dbms_stats.gather_table_stats(null, 'connections');

 

 

 

3. SQL Property Graphs

vertex table이나 edge table 모두 table 오브젝트 뿐만 아니라, external table이나, materialized view을 가지고도 생성이 가능하다. 

 

 

 

CREATE PROPERTY GRAPH CONNECTIONS_PG
	VERTEX TABLES (
    	PEOPLE
        key (person_id)
        label person
        properties all columns
    )
    EDGE TABLES (
    	CONNECTIONS
        key (connection_id)
        source key (person_id_1)
        references people (person_id)
        destination key (person_id_2)
        references people (person_id)
        label connection
        properties all columns
    );

 

column graph_name format a20
column graph_mode format a10
column allows_mixed_types format a17
column inmemory format a8

select * from user_property_graphs;

 

 

 

4. SQL/PGA (GRAPH_TABLE)

/* This query displays all connections of people. */

SELECT	person1, person2
FROM	graph_table (
	connections_pg
	match
	(p1 is person) - [c is connection] -> (p2 is person)
	columns (p1.name as person1,
		p2.name as person2)
	)
ORDER BY 1;

 

 

/* This query displays the connections that Jean Gray firstly connect to the other person */

SELECT	person1, person2
FROM	graph_table(
	connections_pg
	match
	(p1 is person where p1.name = 'Jean Grey') - [c is connection] -> (p2 is person)
	columns (p1.name as person1,
		p2.name as person2)
	)
ORDER BY 1;

 

 

/* This query displays the connection that Jean Gray connect to Clark Kent. */

SELECT	person1, person2
FROM	graph_table(
	connections_pg
	match
	(p1 is person where p1.name = 'Jean Grey') - [c is connection] -> (p2 is person)
	columns (p1.name as person1,
		p2.name as person2)
	)
WHERE 	person2 = 'Clark Kent'
ORDER BY 1;

 

 

/* This query displays the connection and key-value raw data.  */

SELECT	person1, person2, id_p1, id_c, id_p2
FROM	graph_table(
	connections_pg
	match
	(p1 is person) - [c is connection] -> (p2 is person)
	columns (p1.name as person1,
		p2.name as person2,
		vertex_id(p1) AS id_p1,
                 	edge_id(c) AS id_c,
                 	vertex_id(p2) AS id_p2)
	)
WHERE 	rownum = 1
ORDER BY 1;

 

 

 

 

 

 

 

References

SQL Property Graphs and SQL/PGQ in Oracle Database 23c

Graph Database Applications with SQL/PGQ