1. SQL Reference Guide
  2. Built-in Functions
  3. Functions on Rows

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:

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: 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.

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:

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:

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

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:

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:

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:

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 total number of bytes used to store all the rows of the table contained in that partition is retrieved. 
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.