STV_TBL_TRANS - Amazon Redshift

STV_TBL_TRANS

Use the STV_TBL_TRANS table to find out information about the transient database tables that are currently in memory.

Transient tables are typically temporary row sets that are used as intermediate results while a query runs. STV_TBL_TRANS differs from STV_TBL_PERM in that STV_TBL_PERM contains information about permanent database tables.

STV_TBL_TRANS is visible only to superusers. For more information, see Visibility of data in system tables and views.

Table columns

Column name Data type Description
slice integer Node slice allocated to the table.
id integer Table ID.
rows bigint Number of data rows in the table.
size bigint Number of bytes allocated to the table.
query_id bigint Query ID.
ref_cnt integer Number of references.
from_suspended integer Whether or not this table was created during a query that is now suspended.
prep_swap integer Whether or not this transient table is prepared to swap to disk if needed. (The swap will only occur in situations where memory is low.)

Sample queries

To view transient table information for a query with a query ID of 90, type the following command:

select slice, id, rows, size, query_id, ref_cnt from stv_tbl_trans where query_id = 90;

This query returns the transient table information for query 90, as shown in the following sample output:

slice | id | rows | size | query_ | ref_ | from_ | prep_ | | | | id | cnt | suspended | swap ------+----+------+------+--------+------+-----------+------- 1013 | 95 | 0 | 0 | 90 | 4 | 0 | 0 7 | 96 | 0 | 0 | 90 | 4 | 0 | 0 10 | 96 | 0 | 0 | 90 | 4 | 0 | 0 17 | 96 | 0 | 0 | 90 | 4 | 0 | 0 14 | 96 | 0 | 0 | 90 | 4 | 0 | 0 3 | 96 | 0 | 0 | 90 | 4 | 0 | 0 1013 | 99 | 0 | 0 | 90 | 4 | 0 | 0 9 | 96 | 0 | 0 | 90 | 4 | 0 | 0 5 | 96 | 0 | 0 | 90 | 4 | 0 | 0 19 | 96 | 0 | 0 | 90 | 4 | 0 | 0 2 | 96 | 0 | 0 | 90 | 4 | 0 | 0 1013 | 98 | 0 | 0 | 90 | 4 | 0 | 0 13 | 96 | 0 | 0 | 90 | 4 | 0 | 0 1 | 96 | 0 | 0 | 90 | 4 | 0 | 0 1013 | 96 | 0 | 0 | 90 | 4 | 0 | 0 6 | 96 | 0 | 0 | 90 | 4 | 0 | 0 11 | 96 | 0 | 0 | 90 | 4 | 0 | 0 15 | 96 | 0 | 0 | 90 | 4 | 0 | 0 18 | 96 | 0 | 0 | 90 | 4 | 0 | 0

In this example, you can see that the query data involves tables 95, 96, and 98. Because zero bytes are allocated to this table, this query can run in memory.