When you migrate the DB from MongoDB to oracle 23ai, the Indexes "are not" automatically moved. You need to create each one of them after moving the data.
To complete this step in the migration, you need to identify the indexes from Mongodb.
There are several ways to index JSON data, you can use a B-tree or bitmap function-based index for SQL/JSON function
json_value queries. Such an index targets a single scalar JSON value. A bitmap index can be appropriate wherever the number of possible values for the function is small. For example, you can use a bitmap index for
json_value if the values targeted are expected to be few. Please check below as example.First you need identify the indexes from MongoDB
You can use db.<collection>.getIndexes() to identify the Indexes. For example:
Next step, you need to create indexes on Oracle23ai DB.
Note: You can index scalar values in your JSON data using function-based indexes. In addition, you can define a JSON search index, which is useful for both ad hoc structural queries and full-text queries.
For this example, the employee collection was created.
db.createCollection('employee');
db.employee.insertOne({"name":"Carlos Flores","job": "Intern","salary":30000});
db.employee.insertOne({"name":"Armando Castro","job": "Programmer","salary": 60000,"email" : "acastro@oracle.com"});
db.employee.insertOne({"name":"Miguel Lopez","job": "Manager","salary": 70000});
db.employee.insertOne({"name":"Luis Flores","job": "Intern","salary":40000});
db.employee.insertOne({"name":"Patricio Hernandez","job": "Programmer","salary": 70000,"email" : "phernandez@oracle.com"});
db.employee.insertOne({"name":"Hugo Sanchez","job": "Manager","salary": 90000});
Create an index (using the reference from Mongo), for this case:
create index "idx_emp_name" on "PT"."employee" (JSON_VALUE("DATA",'$.name' returning VARCHAR2(60) ERROR ON ERROR NULL ON EMPTY) ASC,1)
Execution Plan Indication that a JSON Search Index Is used, you can validate the creation, including the execution plan:
set lines 150;
set trimout on;
set pagesize 10000;
set tab off;
set wrap on;
COLUMN ID FORMAT A30
COLUMN NAME FORMAT A10
COLUMN JOB FORMAT A15
COLUMN SALARY FORMAT 999,999.99
COLUMN EMAIL FORMAT A30
SET AUTOTRACE ON EXPLAIN
select e.data."_id".string() ID, e.data.name.string() NAME, e.data.job.string() JOB, e.data.salary.number() SALARY, e.data.email.string() EMAIL
from employee e
where e.data.name.string()= 'Hugo Sanchez';
db.employee.getIndexes();
For more references, you can check:
References:
-------------
JSON Indexes
https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/indexes-json-data.html
Creating Indexes for JSON Collections
Creating Indexes for JSON Collections with SQLDeveloperWeb
http://localhost:8081/ords/sql-developer
JSON Search Index
Indexing and partitioning of JSON Collection Tables
