Oracle Database 23ai offers 4 main ways of tuning SQL queries over JSON data: (1) indexes, (2) materialized views, (3) in-memory column storage, and (4) Exadata storage-cell pushdown. This blog post looks into how to index JSON.
It is not a surprise to anyone that the Oracle Autonomous Database makes it extremely easy to index JSON data:
Prior to Oracle Database 21c, we were not able to create a multivalue index for fields which may occur multiple times in a document because they are contained in objects within an array (objects as elements or at lower levels within elements). Back then, we sometimes created a materialized view that extracts the data you want to index, and then create a function-based index on that view data.
With the new Oracle Database 23ai, things have changed and now we can create multivalue indexes: either using SQL or via the MongoDB API.
Let us consider the following collection of different coffee types, their prices, sizes, quantity, and note that couple of them (Espresso and Finlandia) have an additional field with some extra details.
Here is how the data looks from MongoDB Compass:
And here is the insert statement (using mongosh) for creating the same data set.
Let us create (using SQL) a multivalue index for the values of the field “Code”:
CREATE MULTIVALUE INDEX MVI_Code ON coffee c (c.data.CoffeeItems.Details.Code.numberOnly());
Let us verify that the index is actually created and also being utilized using mongosh:
Using index hints is possible with a MongoDB hint that names the index. Note that Oracle does not support the use of MongoDB index specifications — just provide the index name.
db.COFFEE.find({“CoffeeItems.Details.Code”:35801}).hint(“MVI_CODE”);
Verify with:
db.COFFEE.find({“CoffeeItems.Details.Code”:35801}).hint(“MVI_CODE”).explain();
Finally, you can specify an Oracle SQL hint by using the query-by-example (QBE) operator $native which is an Oracle extension to the MongoDB hint syntax. You can pass any SQL hint using $native. In particular, you can turn on monitoring for the current SQL statement using hint MONITOR. This code does that for a query looking for any coffee with a price of 10:
Check here for more details on how to index JSON data.
Read MoreJulian Dontcheff’s Database Blog
