5.1.1 Property Graph Tables (Detailed Information)
After a property graph is established in the database, several tables are created automatically in the user's schema, with the graph name as the prefix and VT$ or GE$ as the suffix. For example, for a graph named myGraph
, table myGraphVT$ is created to store vertices and their properties (K/V pairs), and table myGraphGE$ is created to store edges and their properties.
Additional internal tables are created with IT$ and GT$ suffixes, to store text index metadata and graph skeleton (topological structure).
The definitions of tables myGraphVT$ and myGraphGE$ are as follows. They are important for SQL-based analytics and SQL-based property graph query. In both the VT$ and GE$ tables, VTS, VTE, and FE are reserved columns; column SL is for the security label; and columns K, T, V, VN, and VT together store all information about a property (K/V pair) of a graph element. In the VT$ table, VID is a long integer for storing the vertex ID. In the GE$ table, EID, SVID, and DVID are long integer columns for storing edge ID, source (from) vertex ID, and destination (to) vertex ID, respectively.
SQL> describe myGraphVT$
Name Null? Type
----------------------------------------- -------- ----------------------------
VID NOT NULL NUMBER
K NVARCHAR2(3100)
T NUMBER(38)
V NVARCHAR2(15000)
VN NUMBER
VT TIMESTAMP(6) WITH TIME ZONE
SL NUMBER
VTS DATE
VTE DATE
FE NVARCHAR2(4000)
SQL> describe myGraphGE$
Name Null? Type
----------------------------------------- -------- ----------------------------
EID NOT NULL NUMBER
SVID NOT NULL NUMBER
DVID NOT NULL NUMBER
EL NVARCHAR2(3100)
K NVARCHAR2(3100)
T NUMBER(38)
V NVARCHAR2(15000)
VN NUMBER
VT TIMESTAMP(6) WITH TIME ZONE
SL NUMBER
VTS DATE
VTE DATE
FE NVARCHAR2(4000)
For simplicity, only simple graph names are allowed, and they are case insensitive.
In both the VT$ and GE$ tables, Columns K, T, V, VN, VT together store all information about a property (K/V pair) of a graph element, while SL is used for security label, and VTS, VTE, FE are reserved columns.
In the property graph schema design, a property value is stored in the VN column if the value has numeric data type (long, int, double, float, and so on), in the VT column if the value is a timestamp, or in the V column for Strings, boolean and other serializable data types. For better Oracle Text query support, a literal representation of the property value is saved in the V column even if the data type is numeric or timestamp. To differentiate all the supported data types, an integer ID is saved in the T column.
The K column in both VT$ and GE$ tables stores the property key. Each edge must have a label of String type, and the labels are stored in the EL column of the GE$ table.
The T column in both VT$ and GE$ tables is a number representing the data type of the value of the property it describes. For example 1 means the value is a string, 2 means the value is an integer, and so on. Some T column possible values and associated data types are as follows:
-
1: STRING
-
2: INTEGER
-
3: FLOAT
-
4: DOUBLE
-
5: DATE
-
6: BOOLEAN
-
7: LONG
-
8: SHORT
-
9: BYTE
-
10: CHAR
-
20: Spatial data
The VT$ table schema for storing vertices contains these columns:
-
VID, a long column denoting the ID of the vertex.
-
VL, a string column denoting the label of the vertex.
-
K, a string column denoting the name of the property. If there is no property associated to the vertex, the value of this column should be a whitespace.
-
T, a long column denoting the type of the property.
-
V, a string column denoting the value of the property as a String. If the property type is numeric, a String format version of the value is stored in this column. Similarly, if the property is timestamp based, a String format version of the value is stored.
-
VN, a numeric column denoting the value of a numeric property. This column stores the property value only if the property type is numeric.
-
VT, a timestamp with time zone column storing the value of a date time property. This column stores the property value only if the property type is timestamp based.
-
SL, a numeric column reserved for the security label set using Oracle Label Security (for further details on using Security Labels, see Access Control for Property Graph Data (Graph-Level and OLS)).
-
VTS, a timestamp with time zone column reserved for future extensions.
-
VTE, a timestamp with time zone column reserved for future extensions.
-
FE, a string column reserved for future extensions.
The following example inserts rows into a table named CONNECTIONSVT$. It includes T column values 1 through 10 (representing various data types).
INSERT INTO connectionsvt$(vid,k,t,v,vn,vt) VALUES (2001, '1-STRING', 1, 'Some String', NULL, NULL);
INSERT INTO connectionsvt$(vid,k,t,v,vn,vt) VALUES (2001, '2-INTEGER', 2, NULL, 21, NULL);
INSERT INTO connectionsvt$(vid,k,t,v,vn,vt) VALUES (2001, '3-FLOAT', 3, NULL, 21.5, NULL);
INSERT INTO connectionsvt$(vid,k,t,v,vn,vt) VALUES (2001, '4-DOUBLE', 4, NULL, 21.5, NULL);
INSERT INTO connectionsvt$(vid,k,t,v,vn,vt) VALUES (2001, '5-DATE', 5, NULL, NULL, timestamp'2018-07-20 15:32:53.991000');
INSERT INTO connectionsvt$(vid,k,t,v,vn,vt) VALUES (2001, '6-BOOLEAN', 6, 'Y', NULL, NULL);
INSERT INTO connectionsvt$(vid,k,t,v,vn,vt) VALUES (2001, '7-LONG', 7, NULL, 42, NULL);
INSERT INTO connectionsvt$(vid,k,t,v,vn,vt) VALUES (2001, '8-SHORT', 8, NULL, 10, NULL);
INSERT INTO connectionsvt$(vid,k,t,v,vn,vt) VALUES (2001, '9-BYTE', 9, NULL, 10, NULL);
INSERT INTO connectionsvt$(vid,k,t,v,vn,vt) VALUES (2001, '10-CHAR', 10, 'A', NULL, NULL);
...
UPDATE connectionsVT$ SET V = coalesce(v,to_nchar(vn),to_nchar(vt)) WHERE vid=2001;
COMMIT;
The GE$ table schema for storing edges contains these columns:
-
EID, a long column denoting the ID of the edge.
-
SVID, a long column denoting the ID of the outgoing (origin) vertex.
-
DVID, a long column denoting the ID of the incoming (destination) vertex.
-
EL, a string column denoting the label of the edge.
-
K, a string column denoting the name of the property. If there is no property associated to the vertex, the value of this column should be a whitespace.
-
T, a long column denoting the type of the property.
-
V, a string column denoting the value of the property as a String. If the property type is numeric, a String format version of the value is stored in this column. Similarly, if the property is timestamp based, a String format version of the value is stored.
-
VN, a numeric column denoting the value of a numeric property. This column stores the property value only if the property type is numeric.
-
VT, a timestamp with time zone column storing the value of a date time property. This column stores the property value only if the property type is timestamp based.
-
SL, a numeric column reserved for the security label set using Oracle Label Security (for further details on using Security Labels, see Access Control for Property Graph Data (Graph-Level and OLS)).
-
VTS, a timestamp with time zone column column reserved for future extensions.
-
VTE, a timestamp with time zone column reserved for future extensionss.
-
FE, a string column reserved for future extensions.
In addition to the VT$ and GE$ tables, Oracle Spatial and Graph maintains other internal tables.
An internal graph skeleton table, defined with the GT$ suffix, is used to store the topological structure of a graph, and contains these columns:
-
EID, a long column denoting the ID of the edge.
-
EL, a string column denoting the label of the edge.
-
SVID, a long column denoting the ID of the outgoing (origin) vertex.
-
DVID, a long column denoting the ID of the incoming (destination) vertex.
-
ELH, a raw column specifying the hash value of an edge label.
-
ELS, a integer column specifying the edge label size with respect to total of characters.
An internal table, defined with the SS$ suffix, is created for Oracle internal use only.
Parent topic: Property Graph Schema Objects for Oracle Database