Indexes

Proper use of indexes is essential to achieve good performance. When you run the Oracle Database API for MongoDB, issue the MongoDB createIndexes command to create indexes. Most MongoDB index types are supported, subject to the limitations listed in Feature Support.

When you create an index, the Oracle Database API for MongoDB generates a corresponding SQL create json index statement. For example:

db.employees.insertMany([
    {"_id": 1, "name" : "SMITH", "job" : "CLERK", "sal" : 800},
    {"_id": 2, "name" : "ALLEN", "job" : "SALESMAN", "sal" : 1600},
    {"_id": 3, "name" : "ALLEN", "job" : "CLERK", "sal" : 1250},
    {"_id": 4, "name" : "WARD", "job" : "CLERK", "sal" : 1300}
]);

db.employees.createIndex({"job":1}, {"name":"job_index"});

This example creates the employees collection with four documents and then adds an index on the job field. You can create the same index from SQL by using the create json index statement:

db.aggregate([{$sql:`
    create json index job_index on employees e (f.data.job)
`}]);

MongoDB commands such as find and aggregate, as well as SQL queries, can use these indexes. Use the explain command to verify whether a particular command uses an index. Continuing the example:

db.employees.find({job:"SALESMAN"});
[ { _id: 2, name: 'ALLEN', job: 'SALESMAN', sal: 1600 } ]

scott> db.employees.find({job:"SALESMAN"}).explain();
{
  queryPlanner: {
    namespace: 'scott.employees',
    parsedQuery: { job: 'SALESMAN' },
    rewrittenQuery: { job: { '$stringOnly': 'SALESMAN' } },
    generatedSql: `select "DATA",rawtohex("RESID"),"ETAG" from "SCOTT"."employees" where JSON_EXISTS("DATA",'$?(@.job.stringOnly() == $B0)' passing ? as "B0" type(strict))`,
    winningPlan: ' Plan Hash Value  : 3517912025 \n' +
      '\n' +
      '---------------------------------------------------------------------------------------------------------\n' +
      '| Id  | Operation                             | Name                   | Rows | Bytes | Cost | Time     |\n' +
      '---------------------------------------------------------------------------------------------------------\n' +
      '|   0 | SELECT STATEMENT                      |                        |    1 | 24501 |    2 | 00:00:01 |\n' +
      '|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | employees                  |    1 | 24501 |    2 | 00:00:01 |\n' +
      '| * 2 |    INDEX RANGE SCAN (MULTI VALUE)     | $ora:employees.job_index |    1 |       |    1 | 00:00:01 |\n' +
      '---------------------------------------------------------------------------------------------------------\n' +
      '\n' +
      'Predicate Information (identified by operation id):\n' +
      '------------------------------------------\n' +
      `* 2 - access(JSON_QUERY("DATA" /*+ LOB_BY_VALUE */ FORMAT OSON , '$."job"[*]' RETURNING ANY ORA_RAWCOMPARE ASIS WITHOUT ARRAY WRAPPER ERROR ON ERROR PRESENT ON EMPTY NULL ON MISMATCH TYPE(LAX)\n` +
      '  MULTIVALUE)=SYS_CONS_ANY_SCALAR(:1, 4))\n' +
      '\n' +
      '\n' +
      'Notes\n' +
      '-----\n' +
      '- Dynamic sampling used for this statement ( level = 2 )\n' +
      '\n'
  },
  serverInfo: { host: 'localhost', port: 27017, version: '7.0.22' },
  ok: 1
}

The find command retrieves documents in which the job field equals SALESMAN. The explain command shows the generated SQL and query plan, confirming that the plan uses the job index ($ora:employees.job_index).

Indexes created with MongoDB commands are also available to SQL queries. Continuing the example:

db.aggregate([{ $sql: `
    select data
    from employees e
    where e.data.job = 'SALESMAN'
` }]);
[ { _id: 2, name: 'ALLEN', job: 'SALESMAN', sal: 1600 } ]

db.aggregate([{ $sql: `
    select data
    from employees e
    where e.data.job = 'SALESMAN'
` }]).explain();
{
  queryPlanner: {
    namespace: 'scott.1',
    parsedQuery: {},
    generatedSql: "\n    select data\n    from employees e\n    where e.data.job = 'SALESMAN'\n",
    winningPlan: ' Plan Hash Value  : 3517912025 \n' +
      '\n' +
      '---------------------------------------------------------------------------------------------------------\n' +
      '| Id  | Operation                             | Name                   | Rows | Bytes | Cost | Time     |\n' +
      '---------------------------------------------------------------------------------------------------------\n' +
      '|   0 | SELECT STATEMENT                      |                        |    1 | 22489 |    1 | 00:00:01 |\n' +
      '| * 1 |   TABLE ACCESS BY INDEX ROWID BATCHED | employees                  |    1 | 22489 |    1 | 00:00:01 |\n' +
      '| * 2 |    INDEX RANGE SCAN (MULTI VALUE)     | $ora:employees.job_index |    1 |       |    1 | 00:00:01 |\n' +
      '---------------------------------------------------------------------------------------------------------\n' +
      '\n' +
      'Predicate Information (identified by operation id):\n' +
      '------------------------------------------\n' +
      `* 1 - filter(JSON_VALUE("F"."DATA" /*+ LOB_BY_VALUE */ FORMAT OSON , '$.job' RETURNING VARCHAR2(4000) NULL ON ERROR TYPE(STRICT) )='SALESMAN')\n` +
      `* 2 - access(JSON_QUERY("DATA" /*+ LOB_BY_VALUE */ FORMAT OSON , '$."job"[*]' RETURNING ANY ORA_RAWCOMPARE ASIS WITHOUT ARRAY WRAPPER ERROR ON ERROR PRESENT ON EMPTY NULL ON MISMATCH TYPE(LAX)\n` +
      "  MULTIVALUE)=SYS_CONS_ANY_SCALAR('SALESMAN', 4))\n" +
      '\n' +
      '\n' +
      'Notes\n' +
      '-----\n' +
      '- Dynamic sampling used for this statement ( level = 2 )\n' +
      '\n' +
      '\n'
  },
  ok: 1
}

The $sql stage evaluates a SQL query that returns documents where the job attribute equals SALESMAN. The explain plan shows that SQL also uses the job index created by the MongoDB createIndexes command. MongoDB commands and SQL queries share the same indexes.

Index Hints

Use MongoDB hints to influence index selection for find and aggregation commands. Provide the index name or the index specification document. For example:

db.employees.insertMany([
    {"_id": 1, "name" : "SMITH", "job" : "CLERK", "sal" : 800},
    {"_id": 2, "name" : "ALLEN", "job" : "SALESMAN", "sal" : 1600},
    {"_id": 3, "name" : "ALLEN", "job" : "CLERK", "sal" : 1250},
    {"_id": 4, "name" : "WARD", "job" : "CLERK", "sal" : 1300}
]);

db.employees.createIndex({"job":1});

db.employees.createIndex({"name":1});

db.employees.find({"job":"CLERK", "name":"ALLEN"});
[ { _id: 3, name: 'ALLEN', job: 'CLERK', sal: 1250 } ]

db.employees.find({"job":"CLERK", "name":"ALLEN"}).explain();
{
  queryPlanner: {
    winningPlan: 
      ...
      '| * 2 |    INDEX RANGE SCAN (MULTI VALUE)     | $ora:employees.job_1    ' 
      ...
  }
}

db.employees.find({"job":"CLERK", "name":"ALLEN"}).hint("name_1").explain();
{
  queryPlanner: {
    winningPlan: 
      ...
      '| * 2 |    INDEX RANGE SCAN (MULTI VALUE)     | $ora:employees.name_1     ' 
      ...
  }
}

db.employees.find({"job":"CLERK", "name":"ALLEN"}).hint({"name":1}).explain();
{
  queryPlanner: {
    winningPlan:
      ...
      '| * 2 |    INDEX RANGE SCAN (MULTI VALUE)     | $ora:employees.name_1     ' 
      ...
  }
}

The example creates the employees collection with four documents and defines indexes on the job and name fields. A query that filters on both fields uses the job index by default. The example first applies a hint with the index name "name_1", and then applies the same hint again by using the index specification document { "name": 1 }.

If you create an index from SQL or assign a custom name when using createIndexes, specify that exact name in the hint (for example $ora:employees.job_index). Index specification documents such as { name: 1 } apply only to MongoDB indexes that retain default names (such as name_1).

Index Naming

Index name uniqueness rules differ between Oracle Database and MongoDB. In MongoDB, index names are unique within a collection, so different collections in the same database can share an index name. In Oracle Database, index names must be unique within a schema, so collections in the same schema cannot reuse the same index name. To avoid conflicts, the Oracle Database API for MongoDB prefixes generated index names with $ora:<collection name>. When you run listIndexes, the response includes both the local name and the fully qualified name with the prefix. Continuing the example:

db.employees.getIndexes();
[
  {
    v: 2,
    fullName: '$ora:employees.job_1',
    name: 'job_1',
    key: { job: 1 }
  },
  {
    v: 2,
    fullName: '$ora:employees.name_1',
    name: 'name_1',
    key: { name: 1 }
  },
  { v: 2, key: { _id: 1 }, name: '_id_' }
]

The listIndexes output for the employees collection shows the fullName field, which includes the generated prefix, and the name field, which does not include the prefix.