Previous Next JavaScript must be enabled to correctly display this content

## 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"}

row_metadata function

Note:

This feature has been made available to you on a "preview" basis so that you can get early access and provide feedback. It is intended for demonstration and preliminary use only. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this feature and will not be responsible for any loss, costs, or damages incurred due to the use of this feature.

The `row_metadata` function retrieves user-defined metadata for a row. The metadata is created when you supply a JSON string as an input during insert, update, or delete operations using APIs. The `row_metadata` function then returns a JSON construct, allowing you to access elements within the metadata using JSON dot notation and JSON query operators.

Note:

If a row doesn’t contain metadata, the function returns a `null`.
Syntax:

    json row_metadata(AnyRecord)

Semantics:

- AnyRecord: This function expects a row as the input value.
- returnType: JSON

Example 12-40 Fetch the row metadata for a given row

You can use the `row_metadata()` to fetch the metadata for the desired row of a table if row metadata exists.

    SELECT row_metadata($t) AS info FROM stream_acct $t WHERE acct_Id=1

Output:

    {"info":{"modified_by":"John Doe","reviewed_in":"Q1","update_reason":"Account details updated"}}

For more examples, see Using SQL commands on row metadata.

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-41 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-42 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-43 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-44 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-45 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.