Providing guidelines to query rows based on the modification time

[dario-vega]

Table rows in Oracle NoSQL are record values with some additional properties that are not part of the table schema. The modification_time function allows you to see the most recent modification time (in UTC) of a row. If the row has never been modified since its insertion, it returns the insertion time. You may find this useful when you try to find the latest rows modified during a period of time. We recommend specifying ABSOLUTE consistency, which guarantees that all read operations return the most recently written value for a row.

In the following example, we are returning the rows modified in the last 5 days.

SELECT $u myRecord, modification_time($u)
FROM   <myTable>
WHERE modification_time ($u) >= timestamp_add(current_time() , '-5 days')

The duration parameter of timestamp_add function is a String with format [-]( )+, where 'n' is a number and the can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, NANOSECOND or the plural form of these keywords (e.g. YEARS).

Finally, you can create an index on one of the additional properties of a row.

CREATE INDEX idx_modtime ON <myTable> (modification_time())

By creating this index, you can optimize the performance and reduce the number of resources needed to execute the query above (read units).

In the cloud, I validated the performance of the query by using oci cli commands - it returns the data and the usage statistics for the query executed

oci nosql query execute -c  $MY_COMPID --statement "$SQL_STATEMENT" --consistency ABSOLUTE

When using the index, I had the following statistics

"usage": {
      "read-units-consumed": 4,
      "write-units-consumed": 0
    }

Without the index, I will need more resources because I need to read all the data in the table

    "usage": {
      "read-units-consumed": 4100,
      "write-units-consumed": 0
    }

The performance of your query depends on the size of your table and the number of rows retrieved by the predicate.

If you have one or multiple additional criterias, you can validate the benefits of having a Composite Index.

Learn more

https://docs.oracle.com/en/database/other-databases/nosql-database/25.1/sqlreferencefornosql/functions-rows.html
https://docs.oracle.com/en/database/other-databases/nosql-database/25.1/sqlreferencefornosql/index-classification.html