Demo Environment
$ sql hr/hr@192.168.0.200/FREEPDB1
SQLcl: Release 23.2 Production on Sat Oct 07 20:20:22 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL>
SQL> show user
USER is "HR"
SQL>
SQL> set pages 999
SQL> set lines 120
Clean Up
SQL> drop property graph if exists employee_graph;
Property GRAPH dropped.
Create Property Graph
create property graph employee_graph
vertex tables (
HR.EMPLOYEES
key (EMPLOYEE_ID)
properties (EMPLOYEE_ID,FIRST_NAME, LAST_NAME,HIRE_DATE,JOB_ID,SALARY)
)
edge tables (
HR.EMPLOYEES as WORK_FOR
key (EMPLOYEE_ID)
source key (EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID)
destination key (MANAGER_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID)
)
/
SQL> create property graph employee_graph
2 vertex tables (
3 HR.EMPLOYEES
4 key (EMPLOYEE_ID)
5 properties (EMPLOYEE_ID,FIRST_NAME, LAST_NAME,HIRE_DATE,JOB_ID,SALARY)
6 )
7 edge tables (
8 HR.EMPLOYEES as WORK_FOR
9 key (EMPLOYEE_ID)
10 source key (EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID)
11 destination key (MANAGER_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID)
12 )
13* /
Property GRAPH created.
Query Property Graph Example 1 (With Explain Plan)
-- top 20 rows order by mgr's employee_id for direct employees under him/her
select * from graph_table( employee_graph
match (src) - [IS WORK_FOR] -> (dst)
columns (src.employee_id as emp_id, src.first_name||src.last_name as EMP_NAME,
dst.employee_id as mgr_id, dst.first_name||dst.last_name as MGR_NAME)
)
order by mgr_id fetch first 20 rows only;
SQL> select * from graph_table( employee_graph
2 match (src) - [IS WORK_FOR] -> (dst)
3 columns (src.employee_id as emp_id, src.first_name||src.last_name as EMP_NAME,
4 dst.employee_id as mgr_id, dst.first_name||dst.last_name as MGR_NAME)
5 )
6* order by mgr_id fetch first 20 rows only;
EMP_ID EMP_NAME MGR_ID MGR_NAME
_________ ___________________ _________ _____________
101 NeenaYang 100 StevenKing
102 LexGarcia 100 StevenKing
114 DenLi 100 StevenKing
120 MatthewWeiss 100 StevenKing
121 AdamFripp 100 StevenKing
122 PayamKaufling 100 StevenKing
123 ShantaVollman 100 StevenKing
124 KevinMourgos 100 StevenKing
145 JohnSingh 100 StevenKing
146 KarenPartners 100 StevenKing
147 AlbertoErrazuriz 100 StevenKing
148 GeraldCambrault 100 StevenKing
149 EleniZlotkey 100 StevenKing
201 MichaelMartinez 100 StevenKing
108 NancyGruenberg 101 NeenaYang
200 JenniferWhalen 101 NeenaYang
203 SusanJacobs 101 NeenaYang
204 HermannBrown 101 NeenaYang
205 ShelleyHiggins 101 NeenaYang
103 AlexanderJames 102 LexGarcia
20 rows selected.
PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________
Plan hash value: 1451186655
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 2000 | 1 (0)| 00:00:01 |
|* 1 | VIEW | | 20 | 2000 | 1 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 20 | 1820 | 1 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 20 | 1820 | 1 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 642 | 1820 | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 107 | 4173 | 1 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | EMP_EMP_ID_PK | 21 | | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_MANAGER_IX | 6 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 52 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "DST"."EMPLOYEE_ID")<=20)
7 - access("DST"."EMPLOYEE_ID"="MANAGER_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
26 rows selected.
Query Property Graph Example 2 (With Explain Plan)
-- top 20 rows order by emp's employee_id for direct employees under job title 'AD_PRES'
select * from graph_table( employee_graph
match (src) - [IS WORK_FOR] -> (dst where dst.JOB_ID='AD_PRES')
columns (src.employee_id as emp_id, src.first_name||src.last_name as EMP_NAME,
dst.employee_id as mgr_id, dst.first_name||dst.last_name as MGR_NAME)
)
order by emp_id fetch first 20 rows only;
SQL> select * from graph_table( employee_graph
2 match (src) - [IS WORK_FOR] -> (dst where dst.JOB_ID='AD_PRES')
3 columns (src.employee_id as emp_id, src.first_name||src.last_name as EMP_NAME,
4 dst.employee_id as mgr_id, dst.first_name||dst.last_name as MGR_NAME)
5 )
6* order by emp_id fetch first 20 rows only;
EMP_ID EMP_NAME MGR_ID MGR_NAME
_________ ___________________ _________ _____________
101 NeenaYang 100 StevenKing
102 LexGarcia 100 StevenKing
114 DenLi 100 StevenKing
120 MatthewWeiss 100 StevenKing
121 AdamFripp 100 StevenKing
122 PayamKaufling 100 StevenKing
123 ShantaVollman 100 StevenKing
124 KevinMourgos 100 StevenKing
145 JohnSingh 100 StevenKing
146 KarenPartners 100 StevenKing
147 AlbertoErrazuriz 100 StevenKing
148 GeraldCambrault 100 StevenKing
149 EleniZlotkey 100 StevenKing
201 MichaelMartinez 100 StevenKing
14 rows selected.
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________
Plan hash value: 4176738749
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 600 | 1 (0)| 00:00:01 |
|* 1 | VIEW | | 6 | 600 | 1 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 6 | 588 | 1 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 6 | 588 | 1 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 107 | 588 | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 107 | 5564 | 1 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | EMP_EMP_ID_PK | 107 | | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | | 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 46 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "SRC"."EMPLOYEE_ID")<=20)
7 - access("DST"."JOB_ID"='AD_PRES')
8 - filter("DST"."EMPLOYEE_ID"="MANAGER_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Query Property Graph Example 3 (With Explain Plan)
select * from graph_table( employee_graph
match (emp) - [IS WORK_FOR] -> (mgr1) - [IS WORK_FOR] -> (mgr2) - [IS WORK_FOR] -> (mgr3)
columns (emp.employee_id as emp_id, emp.first_name||emp.last_name as EMP_NAME,
mgr1.first_name||mgr1.last_name as L1_MGR_NAME,
mgr2.first_name||mgr2.last_name as L2_MGR_NAME,
mgr3.first_name||mgr3.last_name as L3_MGR_NAME)
)
order by L3_MGR_NAME,L2_MGR_NAME, L1_MGR_NAME fetch first 20 rows only;
SQL> select * from graph_table( employee_graph
2 match (emp) - [IS WORK_FOR] -> (mgr1) - [IS WORK_FOR] -> (mgr2) - [IS WORK_FOR] -> (mgr3)
3 columns (emp.employee_id as emp_id, emp.first_name||emp.last_name as EMP_NAME,
4 mgr1.first_name||mgr1.last_name as L1_MGR_NAME,
5 mgr2.first_name||mgr2.last_name as L2_MGR_NAME,
6 mgr3.first_name||mgr3.last_name as L3_MGR_NAME)
7 )
8* order by L3_MGR_NAME,L2_MGR_NAME, L1_MGR_NAME fetch first 20 rows only;
EMP_ID EMP_NAME L1_MGR_NAME L2_MGR_NAME L3_MGR_NAME
_________ ___________________ _________________ ______________ ______________
104 BruceMiller AlexanderJames LexGarcia StevenKing
107 DianaNguyen AlexanderJames LexGarcia StevenKing
106 ValliJackson AlexanderJames LexGarcia StevenKing
105 DavidWilliams AlexanderJames LexGarcia StevenKing
109 DanielFaviet NancyGruenberg NeenaYang StevenKing
110 JohnChen NancyGruenberg NeenaYang StevenKing
111 IsmaelSciarra NancyGruenberg NeenaYang StevenKing
112 Jose ManuelUrman NancyGruenberg NeenaYang StevenKing
113 LuisPopp NancyGruenberg NeenaYang StevenKing
206 WilliamGietz ShelleyHiggins NeenaYang StevenKing
10 rows selected.
PLAN_TABLE_OUTPUT
__________________________________________________________________________________________________________________
Plan hash value: 1600920025
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 3880 | 4 (50)| 00:00:01 |
| 1 | SORT ORDER BY | | 20 | 3880 | 4 (50)| 00:00:01 |
|* 2 | VIEW | | 20 | 3880 | 3 (34)| 00:00:01 |
|* 3 | WINDOW SORT PUSHED RANK | | 104 | 20280 | 3 (34)| 00:00:01 |
| 4 | NESTED LOOPS | | 104 | 20280 | 2 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 630 | 20280 | 2 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 105 | 15015 | 2 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 106 | 9646 | 2 (0)| 00:00:01 |
| 8 | VIEW | index$_join$_009 | 107 | 4173 | 2 (0)| 00:00:01 |
|* 9 | HASH JOIN | | | | | |
| 10 | INDEX FAST FULL SCAN | EMP_EMP_ID_PK | 107 | 4173 | 1 (0)| 00:00:01 |
| 11 | INDEX FAST FULL SCAN | EMP_NAME_IX | 107 | 4173 | 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 52 | 0 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | EMP_MANAGER_IX | 6 | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID BATCHED | EMPLOYEES | 1 | 52 | 0 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | EMP_MANAGER_IX | 6 | | 0 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | EMP_MANAGER_IX | 6 | | 0 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 52 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
3 - filter(ROW_NUMBER() OVER ( ORDER BY "MGR3"."FIRST_NAME"||"MGR3"."LAST_NAME","MGR2"."FIRST_NAME"|
|"MGR2"."LAST_NAME","MGR1"."FIRST_NAME"||"MGR1"."LAST_NAME")<=20)
9 - access(ROWID=ROWID)
13 - access("MGR3"."EMPLOYEE_ID"="MANAGER_ID")
15 - access("MGR2"."EMPLOYEE_ID"="MANAGER_ID")
16 - access("MGR1"."EMPLOYEE_ID"="MANAGER_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
39 rows selected.
Troubleshooting
ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to perform a database operation without
the necessary privileges.
*Action: Ask your database administrator or designated security
administrator to grant you the necessary privileges
To fix:
SQL> grant create property graph to hr;
Grant succeeded.
References:
- https://github.com/oracle-samples/db-sample-schemas
- https://docs.oracle.com/en/database/oracle/property-graph/23.1/spgdg/sql-ddl-statements-property-graphs.html#GUID-6EEB2B99-C84E-449E-92DE-89A5BBB5C96E
- https://blogs.oracle.com/database/post/get-started-with-property-graphs-in-oracle-database-23c-free-developer-release
No comments:
Post a Comment