1. Graph Developer's Guide for Property Graph
  2. Getting Started with Oracle Property Graphs
  3. Using the Property Graph Schema
  4. Property Graph Schema Objects for Oracle Database
  5. Property Graph Tables (Detailed Information)

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 text index metadata table, created with IT$ suffix, is used to store metadata information on text indexes created using the Oracle Text search engine. It is automatically populated based on the text indexes created. The IT$ table includes the following columns for general information about a text index:

  • EIN, a string column denoting the name of the text index.

  • ET, a numeric column denoting the entities used to build the text index, if it is a vertex (1) or edge (2) text index.

  • IT, a numeric column denoting the type of the text index, if it is an automatic (1) or manual (2) text index.

  • SE, a numeric column denoting the search engine used to index the entities properties (2 indicates Oracle Text).

  • K, a string column denoting the property name used for text indexing.

For Oracle Text-based indexes, the following columns are used to describe the configuration of the text index (for further details on building an Oracle Text-based index, see Configuring Text Indexes Using Oracle Text):

  • PO, a column denoting the preferred owner for the text index configuration settings. By default, the package owner is set to MDSYS.

  • DS, a string column specifying the data store used to build the text index.

  • FIL, a string column specifying the filter used to build the text index.

  • STR, a string column specifying the storage property used to build the text index.

  • WL, a string column specifying the word list used when building the text index.

  • SL, a string column specifying the stop list used to build the text index.

  • LXR, a string column specifying the lexer used by Oracle Text during text indexing.

  • OPTS, a string column specifying additional configuration options.

An internal table, defined with the SS$ suffix, is created for Oracle internal use only.

Parent topic: Property Graph Schema Objects for Oracle Database