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.
timestamp(3) modification_time (AnyRecord)
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.
Example:
The following query returns the most recent modification time of the row with id 10.
SELECT modification_time($u) FROM users $u WHERE id=10;
+--------------------------+
| Column_1 |
+--------------------------+
| 2021-02-26T07:16:37.266Z |
+--------------------------+integer remaining_hours(AnyRecord)
Returns the number of full hours remaining until the row expires. If the row has no expiration time, it returns a negative number.
integer remaining_days(AnyRecord)
Returns the number of full days remaining until the row expires. If the row has no expiration time, it returns a negative number.
timestamp(0) expiration_time(AnyRecord)
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.
long expiration_time_millis(AnyRecord)
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.
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 row(s) 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.
Example:
The following query returns the shard in which the row with id 10 is stored.
SELECT shard($u) FROM users $u WHERE id=10;
+----------+
| Column_1 |
+----------+
| 1 |
+----------+SELECT count(id) FROM users $u WHERE shard($u) =1;integer partition (AnyRecord)
The partition function allows you to see the partition id in which a given row(s) of data is stored.
Example:
The following query returns the partition in which the row with id 10 is stored.
SELECT partition($u) FROM users $u WHERE id=10;
+----------+
| Column_1 |
+----------+
| 6 |
+----------+Example:
The following query returns the list of partitions in the shard with id 1.
SELECT DISTINCT partition ($u) FROM users $u WHERE shard($u)=1;
+----------+
| Column_1 |
+----------+
| 2 |
| 4 |
| 9 |
+----------+integer row_storage_size (AnyRecord)
The row_storage_size function allows you to see the persistent storage size (in bytes) used by the given row(s) of data. It returns an integer value.
Example:
The following query returns the storage size of the row with id 10.
SELECT row_storage_size($u) FROM users $u WHERE id=10;
+----------+
| Column_1 |
+----------+
| 163 |
+----------+integer index_storage_size (AnyRecord, String)
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.
Example:
The following query returns the storage size of the index to the row with id 10. The second argument "myindex1" is the name of the index created on the users table.
sql-> SELECT index_storage_size($u,"myindex1")
FROM users $u WHERE id=10;
+----------+
| Column_1 |
+----------+
| 24 |
+----------+Example 12-1 Row Function Example
The following query returns for each partition, the total number of bytes used to store all the rows of users table contained in that partition. The first column in the output is the list of partition numbers, and the second column is the current size of those partitions. 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.
sql-> SELECT partition($u),
sum(row_storage_size($u))
FROM users $u
GROUP BY partition($u);
+----------+----------+
| Column_1 | Column_2 |
+----------+----------+
| 1 | 143 |
| 6 | 163 |
| 8 | 143 |
+----------+----------+
3 rows returned