Oracle Database API for MongoDB - Part IV

JSON collection tables are, well, collections of documents stored in Oracle’s optimized binary format OSON (OSON is optimized for query and update efficiency and tends to be smaller than BSON). Just like MongoDB stores documents in BSON. The documents are probably all completely self-contained and self-described, including data redundancy within your documents. So JSON collection tables are probably the most natural choice whenever you are considering to migrate from MongoDB to Oracle platform, move your application as-is, including all your documents, and voilà, you are on a modern platform with so much more functionality than only a document store. Enhance and improve your application with whatever is needed, from transparent data partitioning to adding in-depth analytics with SQL. It’s just the beginning.
 
There’s obviously way more I could talk about, but in this blogpost let’s focus on creating Index and verify the explain plan to see if those indexes were used against the SQL queries and MongoDB command.
 
mdb_test2> // Create index using SQL Commands from MongoShell
 
mdb_test2> db.aggregate([{$sql:" create unique index emp_idx on employees e ( json_value(e.data,'$.name.stringOnly()' error on error)) "}]);
[ { result: 0 } ]
 
mdb_test2> // Create index using MongoDB commands from MongoShell
 
mdb_test2> db.departments.createIndex({deptno:1});
[ { result: 0 } ]
 
Let’s verify the execution plan(s) to see if the Index is being picked up for execution.
 
mdb_test2> // Verify index being used using SQL queries from MongoShell
 
mdb_test2> db.aggregate([{$sql:" explain plan for select * from employees e where e.data.name ='Test' "}]);
[ { result: 0 } ]
mdb_test2> db.aggregate([{$sql:" select plan_table_output from table(dbms_xplan.display) "}]);
[
  { PLAN_TABLE_OUTPUT: 'Plan hash value: 1915469587' },
  { PLAN_TABLE_OUTPUT: ' ' },
  {
    PLAN_TABLE_OUTPUT: '-----------------------------------------------------------------------------------------'
  },
  {
    PLAN_TABLE_OUTPUT: '| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |'
  },
  {
    PLAN_TABLE_OUTPUT: '-----------------------------------------------------------------------------------------'
  },
  {
    PLAN_TABLE_OUTPUT: '|   0 | SELECT STATEMENT            |           |     1 |   174 |     2   (0)| 00:00:01 |'
  },
  {
    PLAN_TABLE_OUTPUT: '|*  1 |  TABLE ACCESS BY INDEX ROWID| employees |     1 |   174 |     2   (0)| 00:00:01 |'
  },
  {
    PLAN_TABLE_OUTPUT: '|*  2 |   INDEX UNIQUE SCAN         | EMP_IDX   |     1 |       |     0   (0)| 00:00:01 |'
  },
  {
    PLAN_TABLE_OUTPUT: '-----------------------------------------------------------------------------------------'
  },
  { PLAN_TABLE_OUTPUT: ' ' },
  {
    PLAN_TABLE_OUTPUT: 'Predicate Information (identified by operation id):'
  },
  {
    PLAN_TABLE_OUTPUT: '---------------------------------------------------'
  },
  { PLAN_TABLE_OUTPUT: ' ' },
  {
    PLAN_TABLE_OUTPUT: `   1 - filter(JSON_VALUE("E"."DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.name' `
  },
  {
    PLAN_TABLE_OUTPUT: "              RETURNING VARCHAR2(4000) NULL ON ERROR TYPE(STRICT) )='Test')"
  },
  {
    PLAN_TABLE_OUTPUT: '   2 - access(JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '
  },
  {
    PLAN_TABLE_OUTPUT: "              '$.name.stringOnly()' RETURNING VARCHAR2(4000) ERROR ON ERROR TYPE(LAX) )='Test')"
  },
  { PLAN_TABLE_OUTPUT: ' ' },
  { PLAN_TABLE_OUTPUT: 'Note' },
  { PLAN_TABLE_OUTPUT: '-----' },
  {
    PLAN_TABLE_OUTPUT: '   - dynamic statistics used: dynamic sampling (level=AUTO (SYSTEM))'
  }
]
 
mdb_test2>
 
 
mdb_test2> // Verify index being used using MongoDB commands from MongoShell
 
mdb_test2> db.employees.find({name:'Test'}).explain();
{
  queryPlanner: {
    plannerVersion: 1,
    namespace: 'mdb_test2.employees',
    indexFilterSet: false,
    parsedQuery: { name: { '$stringOnly': 'Test' } },
    rewrittenQuery: { name: { '$stringOnly': 'Test' } },
    winningPlan: {
      stage: 'SELECT STATEMENT',
      inputStage: {
        stage: 'TABLE ACCESS',
        options: 'BY INDEX ROWID',
        source: 'employees',
        columns: `"DATA" /*+ LOB_BY_VALUE */ [JSON,8200], JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$._id' RETURNING ANY ORA_RAWCOMPARE(2000) NO ARRAY ERROR ON ERROR TYPE(LAX) )[RAW,2000], "ETAG"[RAW,16]`,
        filterType: 'filter',
        filter: `JSON_EXISTS2("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$?(@.name.stringOnly() == $B0)' PASSING :1 AS "B0" FALSE ON ERROR TYPE(STRICT) )=1`,
        path: "$._id'",
        query: "$?(@.name.stringOnly() == $B0)'",
        inputStage: {
          stage: 'INDEX',
          options: 'UNIQUE SCAN',
          source: 'EMP_IDX',
          columns: `"employees".ROWID[ROWID,10], JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.name.stringOnly()' RETURNING VARCHAR2(4000) ERROR ON ERROR TYPE(LAX) )[VARCHAR2,4000]`,
          filterType: 'access',
          filter: `JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.name.stringOnly()' RETURNING VARCHAR2(4000) ERROR ON ERROR TYPE(LAX) )=:1`,
          path: "$.name.stringOnly()'"
        }
      }
    },
    rejectPlans: []
  },
  serverInfo: { host: 'localhost', port: 27017, version: '4.2.14' },
  ok: 1
}
mdb_test2>// Verify index being used using MongoDB commands from MongoShell
 
mdb_test2> db.departments.find({deptno:1}).explain();
{
  queryPlanner: {
    plannerVersion: 1,
    namespace: 'mdb_test2.departments',
    indexFilterSet: false,
    parsedQuery: { deptno: { '$numberOnly': 1 } },
    rewrittenQuery: { deptno: { '$numberOnly': 1 } },
    winningPlan: {
      stage: 'SELECT STATEMENT',
      inputStage: {
        stage: 'TABLE ACCESS',
        options: 'BY INDEX ROWID',
        source: 'DEPARTMENTS',
        columns: `"DATA" /*+ LOB_BY_VALUE */ [JSON,8200], JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$._id' RETURNING ANY ORA_RAWCOMPARE(2000) NO ARRAY ERROR ON ERROR TYPE(LAX) )[RAW,2000], "ETAG"[RAW,16]`,
        filterType: 'filter',
        filter: `JSON_EXISTS2("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$?(@.deptno.numberOnly() == $B0)' PASSING :1 AS "B0" FALSE ON ERROR TYPE(STRICT) )=1`,
        path: "$._id'",
        query: "$?(@.deptno.numberOnly() == $B0)'",
        inputStage: {
          stage: 'INDEX',
          options: 'UNIQUE SCAN',
          source: 'DEPT_IDX',
          columns: `"DEPARTMENTS".ROWID[ROWID,10], JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.deptno.number()' RETURNING NUMBER ERROR ON ERROR TYPE(LAX) )[NUMBER,22]`,
          filterType: 'access',
          filter: `JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.deptno.number()' RETURNING NUMBER ERROR ON ERROR TYPE(LAX) )=:1`,
          path: "$.deptno.number()'"
        }
      }
    },
    rejectPlans: []
  },
  serverInfo: { host: 'localhost', port: 27017, version: '4.2.14' },
  ok: 1
}
mdb_test2>
 
This confirms that index created either using SQL commands and MongoDB commands over JSON Collection tables are interoperable.
 
In the next blogpost we will see about migrating these collections to Relational tables and the flexibility associated with it.