Skip to content
Drivine

Oracle

Oracle Database 23.9 or later, over JDBC. Oracle does not speak Cypher: Drivine reads a graph with SQL/PGQ (GRAPH_TABLE) and writes it as rows, since Oracle refuses data manipulation through a property graph. Models, the query DSL and StatelessGraphObjectManager are the same as on the graph engines. Add the driver yourself: com.oracle.database.jdbc:ojdbc11.

Drivine needs 23.9 because Cypher’s randomUUID() is Oracle’s UUID(), a random version 4 UUID, which Oracle added in 23.9. A provider reads the version from its first connection’s metadata, which needs no grant. On an older Oracle, plain SQL still runs, and a Cypher statement is refused with the version found.

database:
  datasources:
    graph:
      type: ORACLE
      host: localhost
      port: 1521
      database-name: FREEPDB1     # the service to connect to
      user-name: app
      password: secret
      default-graph-path: drivine # names the property graph and its tables; this is the default

user-name and database-name are required, and port is 1521 unless given. A cypher-dialect or a protocol means nothing here and is ignored with a warning. The graph’s name names tables, so it is letters, digits and underscores, begins with a letter, and is at most 114 characters.

Drivine makes a connection pool (HikariCP) for the datasource: pool-max connections at most (40 unless given), connection-timeout milliseconds to wait for one (5,000 unless given), and idle-timeout milliseconds before an idle one is closed (the pool’s own default unless given). The pool asks the database for a connection as it is made, so a service that cannot be reached or a user that is turned away fails at startup. Each connection keeps 64 statements prepared (oracle.jdbc.implicitStatementCacheSize): a save is the same block each time, and is neither sent nor parsed again. A Cypher statement compiled to SQL is not kept: Oracle holds a kept statement’s memory for as long as the driver keeps it, from about 4 MB for a compiled statement to over 20 MB for one that reads a long text, and a few dozen of them ran the instance out of memory (ORA-04036, #313). So it is prepared again each time it runs: about 0.3 ms more for a short statement (Oracle reuses its plan; it doesn’t parse it again from scratch). The object manager’s statements are kept, so where the object manager does what you need, it is the faster way to Oracle.

To use a DataSource the application already has, register a provider in code: registry.register(JdbcConnectionProvider("graph", DatabaseType.ORACLE, dataSource, graphName = "drivine")). Drivine does not close a DataSource it was given, and does not configure it: turn the driver’s statement cache on there yourself. A Cypher statement compiled to SQL is kept out of that cache too. It does set each of its sessions, the first time it connects with one, to make no adaptive plan, unless you ask it not to (see the notes below). For a pool of Drivine’s own (HikariCP) without the YAML:

val provider = JdbcConnectionProvider.pooled(
    "graph", DatabaseType.ORACLE, JdbcConnectionProvider.oracleUrl("localhost", 1521, "FREEPDB1"), "app", "secret",
    graphName = "drivine",
)

How a graph is stored. Two tables and the property graph over them, made, where they are not there yet, before the datasource’s first connection: <graph>_node (nid, labels, props, stamp) and <graph>_edge (eid, src, dst, type, props), with labels and props as JSON. Labels, property names and relationship types are data, so @NodeLabels, @PropertyBag and edges.relate work as they do elsewhere. To SQL written by hand the graph has one vertex label, node, and one edge label, edge; a Cypher label is a test on labels and a relationship type a test on type. A node’s stamp is the stamp column, and a load returns it as __drivine.stamp. Deleting a node’s row deletes its relationships with it. A third table, <graph>_schema, records the indexes and constraints Drivine made: see Schema Management. Beside the tables the graph is made with three functions: <graph>_nulls_equal, which a compiled Cypher statement compares two lists or maps that may hold a null by (see the notes below), and <graph>_era and <graph>_era_date, which renumber a year before 1 between Cypher’s numbering and Oracle’s as a date or a moment is read and written. Making them takes CREATE PROCEDURE, which the RESOURCE role grants, as well as the CREATE TABLE the tables take. A store an earlier version made gains those it lacks before the datasource’s first connection, as a new one gains its tables. Where the user that makes the graph may not make a function, the graph is made without them, and Drivine logs a warning once for the graph when a statement would call one, and looks for it again a minute later. Without <graph>_nulls_equal, a statement that writes, or the step of a reduce, compares two lists or maps that hold a null and differ as JSON as false, where Cypher may give null ([null] = [1]), and a read declares the function itself. Without the era functions, a statement gives the same rows, renumbering a year inline, but Oracle takes longer to plan one of temporal arithmetic: seconds, and over a minute for a moment two CALLs deep.

Notes:

  • Each Oracle session Drivine uses makes no adaptive plan (ALTER SESSION SET OPTIMIZER_ADAPTIVE_PLANS = FALSE). Drivine’s pool runs it as it opens a session, and drops a session it fails in; a session of a DataSource you gave Drivine is set when Drivine first connects with it. Once a graph’s tables had grown and shrunk without statistics, Oracle’s adaptive join plan gave no rows for a read that filters a node by a label and a property, where the same plan, fixed, gave them all (#289). A statement of yours that relies on an adaptive plan can ask for one with the hint /*+ OPT_PARAM('optimizer_adaptive_plans' 'true') */. To have Drivine leave the sessions of your DataSource as you set them, pass setsNoAdaptivePlans = false to JdbcConnectionProvider. It is on by default because, without it, a read can come back with no rows and no error; if you turn it off, run that statement in each session yourself (HikariCP’s connectionInitSql, say). Drivine’s own pool sets its sessions either way.
  • A statement written by hand is SQL. Parameters are named $name as in Cypher. A collection is bound as JSON, for JSON_TABLE to read. A column of the JSON type comes back as a map, so SELECT JSON_OBJECT(… RETURNING JSON) hydrates with .transform(MyView::class.java).
  • A CypherStatement is compiled to SQL over the graph’s tables (see Cypher on a relational database) and gives the rows a graph engine gives: a read is a query, and a write (CREATE, MERGE, SET, REMOVE, DELETE) an anonymous PL/SQL block, applied whole or not at all. A RETURN of several items gives a list of them for each row. A text read out of JSON past 4,000 bytes is read whole where it is measured, cut, changed, searched, reversed or compared (=, <>, <, IN …), but not in a statement that writes or the step of a reduce, where a comparison of one past 32 KB is refused, as is one of a list or a map that holds it anywhere; an empty text is null. reduce is a function the query declares, and does not run in a statement that writes, in the step of a reduce, or where its step reads a path. Two lists ordered with <, <=, > or >= are walked item by item by a function the query declares too, so such a comparison is refused in a statement that writes and in the step of a reduce. Two lists or maps that may hold a null are compared item by item, as Cypher compares them: where one holds a null, two written as the same JSON are null at once, and any other two are walked by the graph’s function <graph>_nulls_equal (see above), in a statement that writes and the step of a reduce too. list[i] reads an item where an expression gives the index, and map[key] reads null where an expression gives the key: Oracle’s paths read no key so. What is not compiled yet (a path returned whole, SKIP of an expression…) is refused by name before anything is sent. What the grammar does not read is refused as not Cypher, with the place in the statement it stands at. UNION, CALL { … } (one that writes too), EXISTS { … }, COUNT { … }, FOREACH, =~, shortestPath and allShortestPaths compile, as do quantified relationships (-[:R]->{1,3}) and quantified path patterns ((a) ((x)-[r:R]->(y) WHERE x.v < y.v){1,3} (b)), whose variables are lists of what each repetition matched. A quantified path pattern’s WHERE and properties read no variable from outside it on Oracle, whose recursive queries read nothing of the row they are in, and hold a subquery only where no variable of its MATCH or before it is named, and that walks no variable-length relationship or quantified path pattern of its own; on Postgres they may. CALL { … } IN TRANSACTIONS is one statement here, applied whole, where a graph engine commits each batch as it goes. A list of nodes or relationships (collect(n), nodes(p), [(a)-->(m) | m]) is given as their properties, and unwound to the nodes themselves, so FOREACH (n IN nodes(p) | SET …) writes to them; compared, indexed, searched with IN or iterated, its items are the nodes themselves too, so two with equal properties differ. CALL db.labels() and CALL db.relationshipTypes() compile, alone or within a query with YIELD, and give each label and type something has now, read from the nodes and relationships; any other procedure is refused. A parameter or a variable may be named by a keyword ($limit, MATCH (end)), as in Neo4j. Functions of numbers (exp, log, sin, atan2, round by a mode …) give the floats Neo4j gives, but where Neo4j gives NaN or an infinity (log(0), sqrt(-1), exp(1000)) the statement fails. NaN and the infinities given as parameters, or worked out by dividing a float by zero (0.0 / 0.0), are the floats they are, and are stored as them, as in Neo4j; but %, ^ and functions of numbers given one give null, where Neo4j gives NaN or an infinity. A number taken % by zero is that number, as Oracle’s MOD gives it, where Neo4j gives NaN by a float zero and fails by an integer one. A float divided by an integer zero is an infinity, where Neo4j fails but where both are written in the statement (1.0 / 0). toFloat reads 'nan', 'inf' and 'INFINITY' as NaN and the infinities too, where Neo4j gives null. A MERGE that would make a property NaN fails, as in Neo4j.
  • A property that a RETURN or a WITH returns or passes on (RETURN n.name) is read through its text, which costs about 2.4 to 3.8 times a plain read: 100,000 rows of RETURN n.name take about 490 ms, against 150 ms. This works around an Oracle bug, seen on 23.26.3: a string past about 32 KB taken from JSON as JSON, returned or fed on into more SQL, crashes the session (ORA-03113, with ORA-00600 [17114] or ORA-07445 in the alert log) over two or more rows. Only an item that is the property itself is read so: one inside an item (RETURN [n.name], collect(n.name)) is read as JSON, as before, and isn’t worked around. It goes once an Oracle release no longer crashes on the repro in docs/oracle-long-json-string-crash.md. Such a text still can’t be sorted by, grouped by or made DISTINCT: Oracle sorts and groups JSON only up to about 32 KB, and refuses the statement (ORA-40180), leaving the session open.
  • Writes are worked around for the same bug: a text that a write takes from an unwound parameter’s item, or from another value of its rows, to write it (UNWIND $rows AS r MATCH (n {id: r.id}) SET n.a = r.a, CREATE (:X {a: r.a}), MERGE … SET n.a = r.a) is read from the row’s text, at no cost measured: 1,000 rows set by one statement take as long as before. Any other value, a map set whole by SET n += r among them, is read as before. So is a value that a UNION in a CALL { … } takes from different values of the row in its queries, or that an OPTIONAL CALL returns, which may be null.
  • So are reads of an unwound item’s value: a text that a RETURN or a WITH gives from an unwound parameter’s item, or from another unwound list’s (UNWIND $rows AS r RETURN r.a, WITH r.a AS a, SET n.a = r.a RETURN r.a), is read from the item’s text, at about 1.3 times a plain read: 100,000 rows of RETURN r.a take about 870 ms, against 660 ms. Any other value is read as before, and a value of a map made of a node’s properties and not unwound (WITH n {.a} AS m RETURN m.a) as the item on #531 below has it; after a write, which carries its rows on, such a value is read from the item’s text too. A pattern that matches on such a text (MERGE (n:X {a: r.a})) compares it as JSON, which Oracle refuses past about 32 KB (ORA-40180), leaving the session open.
  • So are reads of a list’s item by its place (#388): an item read by index, by head() or last(), or after a slice, of a list split makes, a stored list, or one a WITH passes on (split(n.bio, 'x')[0], head(n.tags)), is read from the list’s text where it is a text, at about 1.4 to 2.5 times a plain read, and the list is written out whole for it. Any other item is read as before, and so is an unwound item. A list made of a long property ([n.bio, 'y']) still takes the property out as JSON, as above.
  • So are reads of an argument of coalesce() that is a property of a node or a relationship (#459), wherever the coalesce() is (coalesce($p.q, n.bio), size(trim(coalesce(n.bio, ''))), SET n.a = coalesce(n.bio, '')): a text of at most 4,000 bytes is read as a VARCHAR2, at no cost measured (20,000 rows take 72 ms either way), and a longer one through its text. Any other value is read as before. A value of a map (coalesce(m.a, 'z')) is read as the next item has it.
  • So are reads of a property of a node or a relationship that a function or an index gives, or of a map (#531): head([n]).bio, last(ns).bio, [r][0].bio, properties(n).bio, WITH properties(n) AS m RETURN m.bio, wherever they are read (returned, passed on, measured, searched, in a WHERE or an ORDER BY). They are read as JSON, as before, by a path that leaves out a text past 4,000 characters, at no cost measured (20,000 rows of WITH properties(n) AS m RETURN m.bio take 77 to 83 ms, against 72 to 78 ms; 2,000 rows of RETURN head([n]).bio about 3.2 s either way), and such a text through its text. A property of a node or a relationship that a pattern binds, and a value of an unwound item, are read as above. Still not worked around: a map made of a long property and then read (head([n {.bio}]).bio), and = of a long property with a value that may hold a null (head([n]).bio = n.bio), both still close the session, and so does a long text in a map that is itself a property, returned as it is (RETURN n.meta.bio).
  • So are texts reverse() gives (#538), wherever the reverse() is (returned, passed on, measured, searched, collected, in a WHERE, an ORDER BY or a write): a text past 4,000 bytes, reversed in pieces put together as a list, is taken out of that list through its text, and the kind of a property reverse() is given is read by the property’s own path. A text of at most 4,000 bytes is reversed as before, a little faster, as its kind is read by its path too (20,000 rows of RETURN reverse(n.bio) take about 310 ms, against 335 to 340 ms).
  • An OPTIONAL CALL or an OPTIONAL MATCH is joined ON 1 = 1, where Postgres joins ON TRUE. This works around an Oracle bug, seen on 23.26.3: in a scalar subquery, as a COUNT { … } is compiled, a join ON TRUE is refused (ORA-00907) or crashes the session (ORA-03113, with ORA-07445 in the alert log) (#338). It goes once an Oracle release no longer fails the repro in docs/oracle-on-true-in-scalar-subquery-crash.md.
  • A compiled query that reads the graph’s node or relationship table twice or more, and each query of a write that does, carries the hint NO_JSON_TABLE_TRANSFORM. This works around an Oracle bug, seen on 23.26.3: Oracle’s JSON table transformation reads several JSON values of one node’s row by one JSON_TABLE, and so rewritten beside another node’s table the query lost that node’s label and property tests, giving rows its pattern doesn’t have and values of the wrong node, or crashing the session (ORA-03113) (#415). A query of one table carries no hint.
  • A compiled node compared with a node (c = q, c <> q, c IN [q]), or an id with an id (id(c) = id(q)), compares their keys with + 0 on one side and again the other way round, (n4.nid = n2.nid + 0 AND n2.nid = n4.nid + 0), so either node is still read by its key. This works around Oracle rewrites, seen on 23.26.3, of a key equal to a key: Oracle dropped the later node’s join and tested its label in a form that misses a node with more than one label, or read the other node by the key of a node it had dropped, and gave no rows (#575, #576).
  • A compiled DELETE that reads the ids it deletes from its match (DELETE FROM … WHERE nid IN (SELECT n1.nid + 0 …)) reads the key with + 0. This works around an Oracle rewrite of the same family, seen on 23.26: Oracle unnested nid IN (SELECT n1.nid FROM <nodes> n1 WHERE <label test>) into the table itself and found no rows, so MATCH (g:Gone) DETACH DELETE g deleted nothing (#587). That subquery also tests each node’s labels by their text, which the index over labels can’t answer, in a LATERAL’s query too: planned with the index, Oracle crashed the session (ORA-03113, with ORA-07445 [qjsnmvFindOpt()+16] in the alert log) on MATCH (a:X) OPTIONAL MATCH (a)-[r:R]->(:X {k: 2}) DELETE r, and on the DETACH DELETE of an optional match’s node, after transactions that deleted and read nodes of the label had rolled back. The hint NO_ADAPTIVE_PLAN did not prevent it. The subquery then reads the node table whole, once (#595, drivine#612).
  • A compiled node with a label whose key a WHERE sets equal to another node’s (a = b, id(b) = id(a), as a condition the row must pass, not under NOT or beside an OR) tests its labels by their text, which the index over labels can’t answer, where the other node is found by a label, properties, a relationship or its id: Oracle then reads it by its primary key. With the index’s form, Oracle 23.26.3 read that index again for each row of the other node, so MATCH (a:Big) MATCH (b:Big) WHERE a = b took 1.5 s over 4,000 nodes, growing with their square, and takes about 4 ms now (#578). A query that holds such a test carries the hint OPT_PARAM('_optimizer_use_feedback' 'false'): Oracle 23.26’s statistics feedback otherwise planned the second and third runs of a new cursor again from what the first run found, and read the node the slow way, 1.5 s each over 4,000 nodes (#585). The earlier of two such nodes keeps the index’s form, and so does a node equal to one found by nothing else (MATCH (a) MATCH (b:Rare) WHERE a = b), where the index is the way in.
  • The same text form, and the same hint, are written for a node with a label whose id a WHERE compares with a value (id(n) = $id, id(n) IN $ids, UNWIND $ids AS i … WHERE id(n) = i, id(n) = x.k), and for the later of the two labelled ends of a single relationship (MATCH (x:A)-[:R]->(y:B); the earlier where only the later is given properties, as (y:B)<-[:R]-(x:A {k: 1}); both ends of one of either direction). With the index’s form Oracle 23.26 read every node of the label, and the key only as a filter, once for each id or each edge: an UNWIND of 100 ids over 8,000 nodes took 183 ms, about 2 ms now (#580), and MATCH (x:A)-[:R]->(y:B) 1.3 s over 8,000 edges, about 7 ms now (#581). The list of ids an IN reads, and an UNWIND’s whose item an id is set equal to, carries the hint CARDINALITY(<alias> 10): Oracle estimates a list given as a value at 8,168 rows, and otherwise read every node once and hashed it to the ids.
  • A node with a label that a pattern finds by a property (MATCH (n:Person {id: $id}), UNWIND $rows AS row MATCH (n:Person {id: row.id})) is compared by it as JSON alone for now, and Oracle reads the label’s nodes to find it. The compiler can read it through the label’s range index, or a uniqueness constraint over the property alone, where it is told the graph has one; the connection doesn’t tell it yet (#638). Told so, it compares the node as JSON and, beside that, as that index reads it, so Oracle finds the node through the index. Its labels are tested by their text, and an UNWIND whose item is compared so carries the hint CARDINALITY(<alias> 10). Compared as JSON alone, inside a CASE, even a parameter’s value read every node of the label, once for each row of an UNWIND (#617). A row’s value that may be a list, such as one worked out, is let through to the JSON comparison too, which Oracle reads no index beside; one a parameter gives, or a list written of literals, maps, lists and parameters, as APOC’s export writes its rows, is known to hold none where it holds none. An empty text, and one past 4,000 bytes, are compared as JSON alone, as the index holds no text for them. A node found after a WITH that groups is compared as JSON alone (below).
  • A node a compiled MATCH finds after a WITH that groups its rows (one that aggregates or is DISTINCT, or a WITH after one) tests its labels by their text, which the index over labels can’t answer, in its pattern and in a WHERE such as WHERE m:Movie. With the index’s form, Oracle 23.26 sometimes crashed the session building the statement’s cursor (ORA-03113, with ORA-00600 [QJSNMV_GET_MVI_MD:2] in the alert log, or ORA-07445 [qjsnmvFindOpt()+16] after a rolled-back delete), on MATCH (p:Person)-[r:ACTED_IN]->() WITH p, collect(DISTINCT [r]) AS l, count(DISTINCT [r]) AS n MATCH (p)-[r2:ACTED_IN]->(:Movie {title: 'The Matrix'}) RETURN … (#601, #320, drivine#620). Such a node is mostly found by its key, from a node the WITH passed on; one found by its label alone is read from the whole node table. The nodes before the WITH, a WHERE’s label test of a node the WITH passed on, and every other read keep the index.
  • A list worked out on each row by a subquery (keys(n), [i IN range(1, k) | i], [(p)-->(q) | q.name]) is read from a derived table inside a lateral view. This works around an Oracle bug, seen on 23.26.3: a subquery that reads the row, in the select list of a lateral view that begins a subquery’s FROM, was worked out once for the statement and not for each row, so WHERE 'b' IN keys(n) found nothing (#433).
  • A WITH that passes a path on (MATCH p = (a)-->() WITH p, a RETURN nodes(p)) is kept apart from the query that reads it, with the hint NO_MERGE. This works around an Oracle bug, seen on 23.26.3: merged into that query, the subqueries that read the path’s nodes and relationships by the ids the WITH passes on read one row’s path on every row (#537). The plan gains a view and is otherwise the same, and an order the WITH sorts by is kept: 3,000 rows of WITH p, a ORDER BY a.i RETURN nodes(p) take about 90 ms.
  • openCypher’s temporal values are their ISO 8601 text, as the store keeps a moment. date, localtime, time, localdatetime, datetime and duration make one of a text, of a map of its fields (a calendar date’s and a clock’s, not a week’s or an ordinal day’s), or of the moment the statement runs. Their fields read (d.year, t.hour, dur.days), a duration is added to a moment or to another duration, duration.between and truncate work them out, and two moments compare as the moments they are. truncate takes each unit Neo4j does, from millennium to microsecond, and a map of fields to set on the value cut short (date.truncate('month', d, {day: 2})), but not yet a nanosecond, a week’s or a quarter’s field, or a named zone. A zoned value the statement makes sorts as its moment (ORDER BY datetime(n.at)); one read as a property sorts as its text, and so does a map that holds one. A list of them, or a list of lists of them, sorts item by item so in an ORDER BY, min and max. Oracle reads by their places, not with JSON_TABLE, the items of a list that holds temporal values the statement makes, at any depth: a list of them, a list of lists or of maps of them (collect(DISTINCT [l, l])), one a CASE, coalesce or reduce may give, or one taken back out of a map; and the keys of any map, which keys reads from a list Oracle makes of them. Oracle 23 takes many seconds, or minutes, to parse a JSON_TABLE over a list worked out item by item with temporal arithmetic ([i IN range(1, 10) | t + duration({minutes: i})]), and neither a query timeout nor a kill ends the parse (#331, #347). Other lists keep JSON_TABLE, which is about ten times as fast for a short list; read by their places, the keys are faster than JSON_TABLE gave them. A zoned value keeps the offset it was written with; a named zone, such as Europe/Paris, is not read. A graph engine’s driver gives a temporal value as one of its own, and Oracle and Postgres give its text. A value read from a property is of the kind its text tells, so n.day + duration('P1D') adds a day to a date stored as 2024-03-15. A year is numbered as Neo4j and ISO 8601 number it, where Oracle and Postgres keep no year 0: 0000 is 1 BC and -0044 is 45 BC, made, read, worked out, truncated, written and read back (#469). A stored value whose year is 1000 or later is read as before; one of an earlier year is read by its era, so a statement that reads a stored date or moment takes two to three times as long to compile, and an aggregate of temporal arithmetic runs 8 to 10% slower (a truncate on Postgres, about 16%). Oracle counts days before 1582-10-15 by the Julian calendar, so the day of the week, the ISO week, the day of the year and a leap day of such a date are not Neo4j’s. On every SQL store, two stored values BC compare by their text, which orders them backwards (#374). Two values the statement makes neither of temporal (by a constructor, or a parameter of a java.time type) are not added, taken away, scaled or compared as moments.
  • A statement that writes and returns rows is a PL/SQL block that hands them back with DBMS_SQL.RETURN_RESULT. A block is one statement: one that fails writes nothing.
  • A raw whereClause is a SQL predicate. A property of the root may be written as Cypher writes it (issue.state = 'open'); anything more is SQL over issue.props, issue.labels and issue.stamp.
  • A save is one statement and checks the stamp, as on the graph engines. Another writer that changes a node in SQL should give it a new stamp too: the first sixteen characters of stamp for its properties or labels, the last sixteen for its relationships. Stamps.setClause and Stamps.linksClause are Cypher and do not apply.
  • A save the database turns away because another writer held a row it wanted, a deadlock (ORA-00060) or a serialization failure (ORA-08177), is run again, as on Neo4j and Memgraph.
  • saveAll saves fragments 500 to a statement, and each view by a statement of its own. The batch is one transaction.
  • patchAll writes 500 nodes to a statement, and 100 where a @VectorIndex field is written, in one transaction.
  • patch is one block. Its predicate is run inside the block as a statement of its own, since Oracle Free 23.26 takes no parameter inside a GRAPH_TABLE of a block’s own SQL, which is how a predicate on a relationship is written.
  • A delete can cascade with DELETE_ALL and with DELETE_ORPHAN.
  • Index the id of each node type you save: constraints.ensure(UniquenessConstraintSpec("Person", "id")) makes a unique index and the index a node is found by. Without one a save reads the table.
  • A range index reads a property as text. A comparison or an order by a number or a moment is correct and is not served by that index, so an ordered or keyset-paged load is given no index advice.
  • A range index is two indexes on Oracle: one over the property whatever the label, which a save and a load by id find a node by, and one over the property of the label’s nodes alone. A loadAll, a count, a deleteAll or a groups whose where compares an indexed property with a text (eq, gt, gte, lt, lte) reads the second, and does not test the label on its own: the one index answers both. What else the where holds is tested on the nodes it gives. IN, neq, a test of part of a text, a comparison inside an or, and one with a number are read as before. A uniqueness constraint over one property already has an index over the label’s nodes, its unique index, which reads the same value: a filter on that property is read through it, and no second one is made. A range index made by an earlier version is found, listed and dropped as it is, and the next ensure of it adds the index over the label’s nodes. The backend reads which there are from <graph>_schema on a thread of its own, at most once a minute and again once this process has made or dropped an index or a constraint. A read never waits for it: until the first is done, and while another is under way, statements are written with what was read last.
  • Oracle reads an empty string as null, so a stamp of '' is no stamp. A comparison with "" in the query DSL is written to find a property stored empty all the same; SQL of yours that compares JSON_VALUE(…) with '' finds nothing.
  • matches takes a regular expression in Oracle’s POSIX dialect, matched against the whole value. A leading (?i) ignores case; what Java adds, such as (?:…), a look-around or \b, is not read. A backreference names the group the expression numbers it by, \1 to \8; \9 is refused, as the group that anchors the expression would make it the tenth, which POSIX does not have.
  • A vector index (indexes.ensure(VectorIndexSpec(...)), or @VectorIndex through schema management) is a table of embeddings beside the nodes, kept in step by a trigger, with an Oracle vector index over it by neighbour partitions. The table holds the embeddings of the index’s size; a node written with another size is left out, not refused. loadNearest goes through it and is approximate, as on the graph engines. With no index made, or one of another size than the vector’s, it measures every node of the label whose embedding is of the vector’s size, and is exact.
  • A full-text index is a table of the indexed text beside the nodes, with an Oracle Text index over it. loadMatching reads words, a "phrase", +must, -not and a prefix*. A word is a run of letters as Unicode’s Alphabetic holds them (with the vowel signs of Indic scripts), decimal digits and underscores, as the text’s [[:alnum:]_] reads it: m²x is the phrase m x. In a phrase a * is text, as on Neo4j: "of sou*" is the phrase of sou. A backslash before punctuation or a space makes that character text: prefix\* is the word and \-not is not taken away. A backslash before a letter or a digit is a gap between two words, where Lucene drops it: foo\bar is the phrase foo bar here and the word foobar on Neo4j. The index holds what has been committed, so a search does not see its own transaction’s writes. Oracle Text is in the regular Free image and not the slim one: without it, or with no index made, a search counts the words in the text of every node of the label, scores them by BM25 as Postgres does, and does see them. An analyzer is recorded with the index and not applied.
  • A property over 4,000 characters is stored and loaded whole, and compares as absent in a predicate.
  • The deprecated GraphObjectManager.save and saveAll throw UnsupportedOperationException on Oracle; that manager’s loads, queries and deletes work. Cypher statements written by hand do not run, and neither do the migration tools RelationshipDirectionRepair and PathRelationshipReport, which are Cypher: they repair what saves before 0.1.0 wrote, and no graph on Oracle is that old.

What a read looks like. A load is one SQL/PGQ statement, written to be read beside the Cypher it stands for. A row is called what the model calls it, with an underscore so that a field may be called user or order, and the node of a MATCH has its row’s name. IS node and IS edge stand where Cypher has a label and a type, since both are data: a label is a test of labels, a type of type. doc is the node as the map a load returns. Where Cypher loads a view with

MATCH (claim:Claim) WHERE claim.id = $id
RETURN { claim: claim {.*}, people: [(claim)-[:MENTIONS]->(people:Human) | people {.*}] } AS result

Oracle is sent

SELECT JSON_OBJECT(
    'claim' VALUE claim_.doc,
    'people' VALUE COALESCE((
        SELECT JSON_ARRAYAGG(people_.doc ORDER BY people_.eid RETURNING JSON)
        FROM GRAPH_TABLE (drivine
            MATCH (claim_ IS node) -[e IS edge WHERE e.type = 'MENTIONS']-> (people_ IS node WHERE INSTR(',' || LTRIM(RTRIM(JSON_SERIALIZE(people_.labels), ']'), '[') || ',"', ',"Human","') > 0)
            COLUMNS (claim_.nid AS src, e.eid, people_.doc)) people_
        WHERE people_.src = claim_.nid), JSON('[]'))
    RETURNING JSON) AS result
FROM GRAPH_TABLE (drivine
    MATCH (claim_ IS node WHERE INSTR(',' || LTRIM(RTRIM(JSON_SERIALIZE(claim_.labels), ']'), '[') || ',"', ',"Claim","') > 0)
    COLUMNS (claim_.nid, claim_.props, claim_.doc)) claim_
WHERE JSON_VALUE(claim_.props, '$.id') = $id

A relationship field is a query of its own, under the field it fills: Oracle does not let a GRAPH_TABLE name the row it is nested in, so each returns the node it starts from as src and is joined to that row outside. COLUMNS names only what the statement reads of a row, and every column of the root once a whereClause written by hand is in it. A second row for one field is numbered (people2_), as when a condition on people is tested beside the field itself. A label is tested with JSON_EXISTS(… '$[*]?(@ == "Claim")') where every node of the label is read, which the index over labels answers, and with INSTR where the node is found by its id or at the end of a relationship: the label, escaped as JSON_SERIALIZE escapes it, is looked for as one whole label among the labels as JSON_SERIALIZE writes them, its brackets made commas. Where a range index on the label answers the where, the property is read as that index reads it and the label is not tested on its own:

SELECT n_.doc AS result
FROM GRAPH_TABLE (drivine
    MATCH (n_ IS node)
    COLUMNS (n_.labels, n_.props, n_.doc)) n_
WHERE CASE WHEN JSON_EXISTS(n_.labels, '$[*]?(@ == "Issue")') THEN JSON_VALUE(n_.props, '$.repoKey') END = $_p0
  AND JSON_VALUE(n_.props, '$.state') = $_p1

A @GraphPath field, and a list read to a depth, is the path’s own rows grouped by the node reached, so each node is there once however many ways lead to it:

'employers' VALUE COALESCE((
    SELECT JSON_ARRAYAGG(employers_.doc ORDER BY employers_.nid RETURNING JSON)
    FROM (
        SELECT reached_.src, reached_.nid, ANY_VALUE(reached_.doc) AS doc
        FROM GRAPH_TABLE (drivine
            MATCH (claim_ IS node)
                -[e1 IS edge WHERE e1.type = 'MENTIONS']-> (m1 IS node WHERE INSTR(',' || LTRIM(RTRIM(JSON_SERIALIZE(m1.labels), ']'), '[') || ',"', ',"Human","') > 0)
                -[e2 IS edge WHERE e2.type = 'WORKS_AT']-> (reached_ IS node WHERE INSTR(',' || LTRIM(RTRIM(JSON_SERIALIZE(reached_.labels), ']'), '[') || ',"', ',"Company","') > 0)
            COLUMNS (claim_.nid AS src, reached_.nid, reached_.doc)) reached_
        GROUP BY reached_.src, reached_.nid
    ) employers_
    WHERE employers_.src = claim_.nid), JSON('[]'))

Testing. OracleTestContainer starts Oracle Free (gvenzl/oracle-free:23-faststart; another with ORACLE_TEST_IMAGE or -Dtest.oracle.image) and gives a user, drivine, in the service FREEPDB1. @EnableDrivineTestConfig uses it for a datasource of type ORACLE, or a local instance with USE_LOCAL_ORACLE=true (see TEST_CONTAINERS.md). Add the JDBC driver to the test classpath. Without Spring:

val provider = JdbcConnectionProvider.pooled(
    "graph", DatabaseType.ORACLE, OracleTestContainer.getJdbcUrl(),
    OracleTestContainer.getConnectionUsername(), OracleTestContainer.getConnectionPassword(),
)

A test written for the graph engines usually seeds its graph and reads it back in Cypher, in statements of no language, which Oracle takes for SQL. SqlCypherFixtures sends such a statement as Cypher, which is compiled as any CypherStatement is, so the test’s fixtures stay as they are (it was OracleCypherFixtures, which still works, before Postgres kept a graph the same way):

val fixtures = SqlCypherFixtures.over(persistenceManager)   // Cypher fixtures here, everything else to the store
fixtures.execute(QuerySpecification.withStatement("CREATE (:Person {id: 'ada'})-[:KNOWS]->(:Person {id: 'bob'})"))


SqlCypherFixtures.run(persistenceManager, "MATCH (p:Person) RETURN p.id ORDER BY p.id")   // ["ada", "bob"]
SqlCypherFixtures.clear(persistenceManager)                                               // every node and relationship
  • A statement is taken for Cypher when it opens with MATCH (, OPTIONAL MATCH (, CREATE (, MERGE ( (or one of them naming a path, as MATCH p = (), UNWIND, RETURN or a // comment; any other, one that opens with WITH included, goes to the store as SQL.
  • The manager must name its graph (graphName), as one over a JdbcConnectionProvider does.
  • TestCleanup.beforeEach(persistenceManager, "my-test") deletes the nodes whose createdBy is the tag, in SQL on Oracle, as it does in Cypher elsewhere.
  • SqlCypherFixtures.forStore(persistenceManager) is the manager itself on a graph engine and the fixtures over it on Oracle, Postgres or SQLite, so one test, or one PersistenceManager bean of a test’s Spring context, serves every store. Drivine’s sample app and its own Spring tests use it so.

Drivine’s own Oracle tests are left out of a plain ./gradlew test: the image is large and the database wants two gigabytes. Run them with

Terminal window
ORACLE_TESTS=true ./gradlew :test --tests '*Oracle*'

Without it, each test task prints Oracle tests skipped: set ORACLE_TESTS=true to run them. CI runs the same command, in a job of its own (test (Oracle)).