Functions on Rows

As described in the Table Management section, table rows are record values conforming to the table schema, but with some additional properties that are not part of the table schema. To extract the value of such properties, the functions listed in this section must be used.

Although the signature of these functions specifies AnyRecord as the type of the input parameter, the functions actually require a row as input. The only expression that returns a row is a row variable, that is, a table alias whose name starts with '$'. The Example: Updating TTL section shows an example of using the remaining_hours() function, which is one of the row available functions.

modification_time function

The modification_time function allows you to see the most recent modification time (in UTC) of a row. The time is returned as a timestamp value of precision 3 (milliseconds). If the row has never been modified since its insertion, it returns the insertion time. You may find this useful in deployments where tables span multiple regions and the Oracle NoSQL Database cross-region agent is updating your table by replicating data from remote regions.

Syntax:

timestamp(3) modification_time (AnyRecord)

Semantics:

AnyRecord: This function expects a row as the input value.
return type: timestamp (3)

Example 12-37 Fetch the last modified time details for a passenger record from the airline baggage tracking application table

In an airline application, you can schedule periodic back-ups of passenger travel records based on the last modification time. Only the records that have been modified after the last scheduled backup can be considered for the next iteration.

SELECT modification_time($u) 
FROM BaggageInfo $u 
WHERE ticketNo = 1762344493810

Explanation:

You can use the modification_time function to retrieve the last modified time details for a passenger record from the BaggageInfo table.

Output:


 +--------------------------+
 |         Column_1         |
 +--------------------------+
 | 2023-01-18T07:53:02.048Z |
 +--------------------------+

The query returns the information on the most recent modification time for the passenger with ticketNo 1762344493810 from the BaggageInfo table.

remaining_hours function

Returns the number of full hours remaining until the row expires. If the row has no expiration time, it returns a negative number.

Syntax:

integer remaining_hours(AnyRecord)

Semantics:

AnyRecord: This function expects a row as the input value.
return type: integer

remaining_days function

Returns the number of full days remaining until the row expires. If the row has no expiration time, it returns a negative number.

Syntax:

integer remaining_days(AnyRecord)

Semantics:

AnyRecord: This function expects a row as the input value.
return type: integer

expiration_time function

Returns the expiration time of the row, as a timestamp value of precision zero. If the row has no expiration time, it returns a timestamp set on January 1, 1970 UTC.

Syntax:

timestamp(0) expiration_time(AnyRecord)

Semantics:

AnyRecord: This function expects a row as the input value.
return type: timestamp(0)

expiration_time_millis function

Returns the expiration time of the row, as the number of milliseconds since January 1, 1970 UTC. If the row has no expiration time, it returns zero.

Syntax:

long expiration_time_millis(AnyRecord)

Semantics:

AnyRecord: This function expects a row as the input value.
return type: long

Example 12-38 TTL-related functions

In an airline application, you may want to back up the passenger records before the data expiration time or extend the expiration date to retain the information in the table for a longer period.

SELECT
remaining_hours($u) AS hours,
remaining_days($u) AS days,
expiration_time($u) AS expirytime,
expiration_time_millis($u) AS expirytime_ms
FROM BaggageInfo $u
WHERE ticketNo = 1762344493810

Explanation:

You can use the TTL expiration functions to check the expiration details (if any) of the rows containing the passenger records in the BaggageInfo table.

Output:

{"hours":376,"days":15,"expirytime":"2023-02-04T00:00:00.000Z","expirytime_ms":1675468800000}

The query returns the TTL information on the row that contains the passenger data for ticketNo 1762344493810 from the BaggageInfo table.

Here, the row expires after 15 days. The same information is displayed in hours, timestamp value, and number of milliseconds since January 1, 1970, UTC using the row functions described above.

You can update the expiration day/time of a row using the UPDATE statement as follows:

UPDATE BaggageInfo $u
SET TTL remaining_days($u) + 15 days
WHERE ticketNo = 1762344493810

The above statement extends the life of the row by 15 days. In this example, the remaining days until the expiry of the row is 15 days, to which the above statement adds 15 more days, effectively rendering the row to expire after 30 days. You can use the remaining_hours function and add hours to it to extend the expiration of a row by hours.

For more details on updating the TTL values, see Example: Updating TTL.

The following functions allow you to see how the data is distributed across the store and collect statistics:

version or row_version function

The version or row_version function allows you to see the version of a given row. Every time a row is inserted or modified, the row version value is automatically calculated and the previous row version value is replaced with the new value. The row version value is unique for every row within the table and cannot be modified manually. Both version and row_version functions fetch the same result and can be used interchangeably in a query. You can use the version and row_version functions in both SELECT and WHERE clauses as demonstrated in the Example section.

Syntax:

binary version(AnyRecord)

binary row_version(AnyRecord)

Semantics:

AnyRecord: The version or row_version function expects a row as the input value.
return type: The version or row_version function returns a binary value. To use the version as a string in a query, you must cast the value to a string using the CAST expression.

Example 12-39 Fetch the version of the row with passenger ticket number 1762344493810

SELECT row_version($t) from Baggageinfo $t WHERE ticketNo =
    1762344493810

Explanation:

In this query, you fetch the version of the row that includes passenger data with ticket number 1762344493810. You supply the row as an input argument to the row_version function. Using the version function instead will also return the same result.

Output:

{"Column_1":"rO0ABXcsACEZ1TNcQVVExr5UlPEJixAJAAAAAAAAKDIBAwAAAAEAAAABAAAAAAAO1xE="}

In the following example, you use the UPDATE statement to modify the confNo field in a passenger's data and fetch the updated row version.

UPDATE baggageinfo $t
SET confNo = "LE6J6Z"
WHERE ticketNo = 1762344493810
RETURNING row_version($t) as rowVersion

Output:

{"rowVersion":"rO0ABXcsACEZ1TNcQVVExr5UlPEJixAJAAAAAAAATP8BAwAAAAEAAAABAAAAAAAaylA="}

After the row is updated, the version of the corresponding row is replaced with a new value automatically by Oracle NoSQL Database server.

In the following example, you use the row_version function in a WHERE clause to fetch the passenger data associated with a row version.

SELECT ticketNo AS TICKET, fullName AS NAME 
FROM baggageinfo $t 
WHERE cast(row_version($t) as String) ="rO0ABXcsACEZ1TNcQVVExr5UlPEJixAJAAAAAAAAKDIBAwAAAAEAAAABAAAAAAAO1xE="

Notice that you cast the result of the row_version function to a string using the CAST expression to compare the versions. In this example, a previous row version is used to fetch the passenger data. As the previous row version no longer exists when the data is updated, there is no passenger data associated with it.

0 row returned

You can also use the row_version function in other NoSQL expressions. The following example determines if passenger data has been modified using the row_version function in a CASE expression:

SELECT ticketNo AS TICKET, modification_time($t) AS TIME,
CASE
WHEN CAST(row_version($t) as String) = "rO0ABXcsACEZ1TNcQVVExr5UlPEJixAJAAAAAAAAKDIBAwAAAAEAAAABAAAAAAAO1xE="
        THEN "row is not modified"
        ELSE "row is modified"
END AS REPORT
FROM baggageinfo $t
WHERE ticketNo = 1762344493810;

You first cast the result of the row_version function to a string using the CAST expression and then compare the value with the previously stored version. Depending on the result of the string comparison operation, the corresponding message is displayed. This query also uses the modification_time function to see the most recent modification time of the passenger's data.

Output:

{"TICKET":1762344493810,"TIME":"2024-04-04T09:42:52.128Z","REPORT":"row is
      modified"}

integer shard (AnyRecord)

The shard function allows you to retrieve the shard ID in which a given rows of data is stored. It returns an integer value. For more information on shard ID, see the Viewing Key Distribution Statistics section in the Administrator's Guide.

Syntax:

integer shard (AnyRecord)

Semantics:

AnyRecord: This function expects a row as the input value.
return type: integer

Example 12-40 Determine the shard details where the passenger record is stored

You may identify potential storage hotspots or an imbalance in your Oracle NoSQL Database cluster using the function. For example, you may notice that a particular shard seems to consume more storage than any other shard.

SELECT shard($u) AS Shard 
FROM BaggageInfo $u 
WHERE ticketNo = 1762344493810

Explanation:

In an airline application, you use the shard function to determine the shard details where the passenger record is stored.

Output:

{"Shard":1}

The query returns the shard in which the row with ticketNo 1762344493810 is stored in the BaggageInfo table.

You can compare the number of records in that shard with the other shards in your store by issuing the following command.

SELECT count(ticketNo) AS Shard_count 
FROM BaggageInfo $u 
WHERE shard($u) =1

Output:

{"Shard_count":2}

partition function

All data in the KVStore is accessed by one or more Keys. A Key might be a column in a table, or it might be the key portion of a Key/Value pair. The Keys are placed in logical containers called partitions, and each shard contains one or more partitions. For more details, see Partitions in the Concepts Guide.

The partition function allows you to see the partition id in which a given rows of data is stored.

Syntax:

integer partition (AnyRecord)

Semantics:

AnyRecord: This function expects a row as the input value.
return type: integer

Example 12-41 Determine the partitions in which the rows are stored

Use the partition function to determine the partitions in which the rows are stored in Oracle NoSQL Database.

SELECT partition($u) AS partition 
FROM BaggageInfo $u 
WHERE ticketNo = 1762344493810

Explanation:

In this example, the partition function returns the partition details of where the passenger record is stored. The query returns the partition in which the row with ticketNo 1762344493810 is stored in the BaggageInfo table.

Output:

{"partition":80}

The following query returns the list of partitions in the shard with id 1.

SELECT partition ($u) AS partition_count 
FROM BaggageInfo $u 
WHERE shard($u)=1

Output:

{"partition_count":80}
{"partition_count":131}

row_storage_size function

The row_storage_size function allows you to see the persistent storage size (in bytes) used by the given rows of data. It returns an integer value.

Syntax:

integer row_storage_size (AnyRecord)

Semantics:

AnyRecord: This function expects a row as the input value.
return type: integer

Example 12-42 Fetch the record size for a given row

You can use the row_storage_size function to obtain the record size for a given row.

SELECT row_storage_size($u) AS storage_size 
FROM BaggageInfo $u 
WHERE ticketNo = 1762344493810

Explanation:

In an airline application, you can use the row_storage_size function to determine the storage size of the individual passenger records.

Output:

{"storage_size":1123}

The query returns the storage size of the row containing the passenger record with ticketNo 1762344493810 in the BaggageInfo table. The storage size is displayed in bytes.

index_storage_size function

The index_storage_size function allows you to see the persistent storage size (in bytes) used by the index for the given row(s) of data. It returns an integer value. This function takes two arguments. The first argument is the table reference in which the index is created. The second argument is the index name for which the storage size is required. The index name is case-insensitive.

Syntax:

integer index_storage_size (AnyRecord, String)

Semantics:

AnyRecord: A table reference in which the index is created as its first argument.
string: The name of the index.
return type: integer

Example 12-43 Determine the storage size of the individual index on a table

You can use the index_storage_size function to determine the storage size of the individual index on a table. The storage size of each index must be queried separately. To determine the total storage size of all the indexes on a table, you have to call the function for every index.

SELECT index_storage_size($u,"idx_contact") 
FROM baggageInfo $u 
WHERE ticketNo = 1762344493810

Explanation:

In an airline application, you can create an index for required fields on your Oracle NoSQL Database table. You use the index_storage_size function to retrieve the storage size of each index.

In this example, an index is created on the contactPhone field in the table. The table name BaggageInfo table and the index name idx_contact are supplied as arguments to the function.

For more information on Indexes, see About Indexes.

Output:


 +----------+
 | Column_1 |
 +----------+
 |       40 |
 +----------+

The query returns the storage size of the row containing the passenger record with ticketNo 1762344493810 in the BaggageInfo table. The storage size is displayed in bytes.

Example 12-44 Fetch the total number of bytes used to store all the rows of the table in a partition

In an airline application, you can get a detailed view of how your storage is distributed for your table across the partitions in your cluster by using the following query. For each partition, the query retrieves the total number of bytes used to store all the rows of the table contained in that partition.

SELECT
partition($u) AS partition,
sum(row_storage_size($u)) AS sum
FROM BaggageInfo $u
GROUP BY partition($u)

Explanation:

In this example, the partition function determines the partitions in which the passenger records are stored in the BaggageInfo table. The sum function computes the storage size of each row and sums up the result.

Output:


 +----------+----------+
 | partition|     sum  |
 +----------+----------+
 |        80|      1123|
 |       131|      1115|
 +----------+----------+

The first column in the output is the list of partition numbers, and the second column is the current size of those partitions.