Count the Number of Parts using Graphs
In my previous article, “Powerful Graph Search for Traceability in Manufacturing”, I introduced how Oracle Graph can achieve high-performance path searches on BoM (bill of materials) and supply chain networks.
In this article, we will consider how to calculate the number of required components using BoM efficiently. This counting seems simple, but it needs to be performed repeatedly in many cases, such as for calculating the cost and weight difference when changes are made in the design, or for measuring the impact of changes in the number of parts that can be procured.
Example BoM
Let’s consider the following BoM. The number on the edges indicates the number of child parts required for the parent part.
This graph is stored in a database with table format as follows.
BOM_NODE:
ID PRICE
----- ----------
X
A
B
C
D
E
F 200
G
H 150
I 120
...BOM_EDGE:
ID PAREN CHILD CNT
---------- ----- ----- ----------
1 G K 2
2 X A 1
3 X B 2
4 X C 4
5 A D 2
6 A E 2
7 B E 4
8 C F 2
9 C G 3
10 D H 1
...See this GitHub repository for the entire script to load this table data.
Calculation Method
Let’s find the number of Part E as an example. We need 2 units of Part E for 1 unit of Part A, and 8 units of Part E (= 2 * 4) for 2 units of Part B. Therefore, the total is 2 + 8 = 10 units.
In other words, to find the required number of a certain part, you need to obtain all paths between the root model X and the part, multiply the edge values for each path, and then sum them up.
Using this method, you can find the required number of Part K as follows.
(1 * 2 * 4) + (2 * 4 * 4) + (4 * 3 * 2) = 64Here, graph databases can efficiently search for paths of unknown length, so that this feature will be useful for the calculation above.
Creating a Graph
Now, we will create a graph from the previously shown tables, bom_node and bom_edge. The graph name will be bom_graph. This syntax is available as PGQL in Oracle Database 12.2 and above and is also available as an SQL standard from 23c.
CREATE PROPERTY GRAPH bom_graph
VERTEX TABLES (
bom_node
KEY (id)
LABEL part
PROPERTIES (id, price)
)
EDGE TABLES (
bom_edge
KEY (id)
SOURCE KEY(parent) REFERENCES bom_node (id)
DESTINATION KEY(child) REFERENCES bom_node (id)
LABEL has_part
PROPERTIES (cnt)
)PGQL Query
Let’s take a look at the PGQL query for that calculation and its result.
SELECT
p2.id AS id
, LISTAGG(LISTAGG(CAST(e.cnt AS INTEGER), '*'), ' + ')||' =' AS fomula
, SUM(my.product(LISTAGG(CAST(e.cnt AS INTEGER), ','))) AS total_cnt
FROM MATCH ALL (p1) -[e:has_part]->{1,100} (p2) ON bom_graph
WHERE p1.id = 'X'
GROUP BY p2.id
ORDER BY p2.id+------------------------------------------+
| id | fomula | total_cnt |
+------------------------------------------+
| A | 1 = | 1 |
| B | 2 = | 2 |
| C | 4 = | 4 |
| D | 1*2 = | 2 |
| E | 1*2 + 2*4 = | 10 |
| F | 4*2 = | 8 |
| G | 4*3 = | 12 |
| H | 1*2*1 = | 2 |
| I | 1*2*4 = | 8 |
| J | 1*2*2 + 2*4*2 = | 20 |
| K | 1*2*4 + 2*4*4 + 4*3*2 = | 64 |
| L | 4*3*5 = | 60 |
| M | 4*3*2 = | 24 |
+------------------------------------------+To briefly explain, the following pattern match syntax first searches for all paths between two nodes p1 (= Model X) and p2 (= a certain part) with 1 to 100 hops. Since we need to specify an upper limit when searching for all paths (to avoid infinite loops), it is set to 100.
FROM MATCH ALL (p1) -[e:has_part]->{1,100} (p2) ON bom_graph
WHERE p1.id = 'X'For each of these paths, the list of edge numbers (cnt) is turned into a comma-separated string with LISTAGG first, then their product is calculated by my.product, and finally, sum them up for all paths.
SUM(my.product(LISTAGG(CAST(e.cnt AS INTEGER), ','))) AS total_cntThe function my.product here is a user-defined function (UDF) that returns the product of integers provided as a comma-separated string. For more details on how to use UDFs in PGX (Oracle Graph’s in-memory engine), please refer to my next article, “User-Defined Functions (UDFs) in Oracle Graph”.
If the unit prices of the leaf parts are held in a vertex property price, you can use it to calculate the total cost of each part.
SELECT
p2.id AS id
, LISTAGG(LISTAGG(CAST(e.cnt AS INTEGER), '*'), ' + ')||' =' AS fomula
, SUM(my.product(LISTAGG(CAST(e.cnt AS INTEGER), ','))) AS total_cnt
, p2.price AS unit_price
, SUM(my.product(LISTAGG(CAST(e.cnt AS INTEGER), ','))) * p2.price AS total_price
FROM MATCH ALL (p1) -[e:has_part]->{1,100} (p2) ON bom_graph
WHERE p1.id = 'X'
GROUP BY p2.id, p2.price
ORDER BY p2.id+---------------------------------------------------------------------+
| id | fomula | total_cnt | unit_price | total_price |
+---------------------------------------------------------------------+
| A | 1 = | 1 | 0.0 | 0.0 |
| B | 2 = | 2 | 0.0 | 0.0 |
| C | 4 = | 4 | 0.0 | 0.0 |
| D | 1*2 = | 2 | 0.0 | 0.0 |
| E | 1*2 + 2*4 = | 10 | 0.0 | 0.0 |
| F | 4*2 = | 8 | 200.0 | 1600.0 |
| G | 4*3 = | 12 | 0.0 | 0.0 |
| H | 1*2*1 = | 2 | 150.0 | 300.0 |
| I | 1*2*4 = | 8 | 120.0 | 960.0 |
| J | 1*2*2 + 2*4*2 = | 20 | 50.0 | 1000.0 |
| K | 1*2*4 + 2*4*4 + 4*3*2 = | 64 | 20.0 | 1280.0 |
| L | 4*3*5 = | 60 | 30.0 | 1800.0 |
| M | 4*3*2 = | 24 | 70.0 | 1680.0 |
+---------------------------------------------------------------------+This scenario shows that graphs can be useful even for basic calculations such as counting the number of parts.
We will continue to utilize the graph feature for various use cases. If you are interested, please follow Oracle Graph articles.
Please learn more about Oracle Graph from:
- Medium (= all articles with Oracle Graph tag)
- Stack Overflow (= questions with oracle-graph tag)
- Slack AnDOUC (= invitation link, please visit #graph)
- Slack OracleDevRel (= invitation link, please visit #oracle-db-graph)
