List Table Rows by ROS Container: Quick Tip

Posted January 3, 2019 by Jim Knicely, Vertica Principal Solution Architect

High angle view of Beijing Guomao.
A ROS (Read Optimized Store) container is a set of rows stored in a particular group of files. ROS containers are created by operations like Moveout or COPY DIRECT. You can query the STORAGE_CONTAINERS system table to see ROS containers.

You can use the LEAD_STORAGE_OID function to list the rows from a table that are located in a particular ROS container.

Example: dbadmin=> \! cat /home/dbadmin/t1.txt 1 2 3 dbadmin=> \! cat /home/dbadmin/t2.txt 4 5 dbadmin=> \! cat /home/dbadmin/t3.txt 6 7 8 dbadmin=> CREATE TABLE t (c INT); CREATE TABLE dbadmin=> COPY t FROM '/home/dbadmin/t1.txt'; Rows Loaded ------------- 3 (1 row) dbadmin=> COPY t FROM '/home/dbadmin/t2.txt' DIRECT; Rows Loaded ------------- 2 (1 row) dbadmin=> COPY t FROM '/home/dbadmin/t3.txt' DIRECT; Rows Loaded ------------- 3 (1 row) dbadmin=> SELECT storage_oid, storage_type, total_row_count dbadmin-> FROM storage_containers dbadmin-> WHERE projection_name = 't_super'; storage_oid | storage_type | total_row_count -------------------+--------------+----------------- 45035996280187963 | ROS | 3 45035996280187975 | ROS | 2 45035996280187987 | ROS | 3 (3 rows) dbadmin=> SELECT * dbadmin-> FROM t dbadmin-> WHERE lead_storage_oid() = 45035996280187963; c --- 1 2 3 (3 rows) dbadmin=> SELECT * dbadmin-> FROM t dbadmin-> WHERE lead_storage_oid() = 45035996280187975; c --- 4 5 (2 rows) dbadmin=> SELECT * dbadmin-> FROM t dbadmin-> WHERE lead_storage_oid() = 45035996280187987; c --- 6 7 8 (3 rows) Helpful Links:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/Glossary/ROSContainer.htm

https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/STORAGE_CONTAINERS.htm

Have fun!