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.
timestamp(3) modification_time (AnyRecord)
Semantics:
- AnyRecord: This function expects a row as the input value.
- return type: timestamp(3)
Example:
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.
+--------------------------+
| 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.
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.
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.
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.
long expiration_time_millis(AnyRecord)
Semantics:
- AnyRecord: This function expects a row as the input value.
- return type: long
Example: TTL-related functions
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.
{"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.
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.
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.
{"Shard":1}
The query returns the shard in which the row with ticketNo
1762344493810 is stored in the BaggageInfo
table.
SELECT count(ticketNo) AS Shard_count
FROM BaggageInfo $u
WHERE shard($u) =1;
{"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.
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.
{"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;
{"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.
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.
{"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.
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:
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.
+----------+
| 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:
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.
+----------+----------+
| 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.