SHOW TEMPORAL HISTOGRAM

Use the SHOW TEMPORAL HISTOGRAM command on a temporal table to see the space that was used by deleted rows over the past days, up to the retention time interval of the table.

For more information about temporal tables and time travel, see Getting started with Netezza Performance Server time travel.

Syntax

Syntax for the SHOW TEMPORAL HISTOGRAM command:

SHOW TEMPORAL HISTOGRAM <TABLE NAME> [DAYSPERROW <number of days>]

Inputs

The SHOW TEMPORAL HISTOGRAM command takes the following inputs:
Input Description
<name> Identifies the temporal table under the current schema and the connected database.

If a table is outside of the current scoped database or schema, you might use the database.schema.tablename qualifier to identify the table.

You cannot specify a database name in this place.
DAYSPERROW

Allows for one histogram row every number of days (<numdays>).

For example, every week.

The default value is 1 day.

Outputs

Output Description
BASE_TIMESTAMP Displays the timestamp of the query, relative to which STARTDAYSAGO and ENDDAYSAGO are interpreted.
STARTDAYSAGO Displays the start of the day range that is covered by this row.
ENDDAYSAGO Displays the end of the day range that is covered by this row.
DELETEDROWS Displays how many rows (count) are deleted during the day range.
MBYTESUSED Displays the estimated number of megabytes that are consumed by the deleted rows.
USAGEPERCENT Displays as a percentage how many rows are deleted as compared to the total number of (deleted and current) rows in the table.

Privileges

Similarly to SELECT (to retrieve rows), you must be the admin user, the table owner, the owner of the database or schema where the table is defined, or your account must have Select privileges on the table or the Table object class.

Usage

  • show temporal histogram flight;
        BASE_TIMESTAMP      | STARTDAYSAGO | ENDDAYSAGO | DELETEDROWS | MBYTESUSED | USAGEPERCENT
----------------------------+--------------+------------+-------------+------------+--------------
 2022-11-08 15:35:59.442782 |            1 |          0 |         503 |    144.230 |        19.23
 2022-11-08 15:35:59.442782 |            2 |          1 |           0 |          0 |            0
 2022-11-08 15:35:59.442782 |            3 |          2 |         451 |    110.294 |        14.71
 2022-11-08 15:35:59.442782 |            4 |          3 |         913 |    218.529 |        29.14
 2022-11-08 15:35:59.442782 |            5 |          4 |           0 |          0 |            0
 2022-11-08 15:35:59.442782 |            6 |          5 |         389 |     88.235 |        11.76