List Table Rows by ROS Container

Jim_Knicely

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/9.2.x/HTML/Content/Authoring/Glossary/ROSContainer.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/STORAGE_CONTAINERS.htm

Have fun!

RaghavA

Hello Jim,

Is it also available for v8.1.x ?

Regards

Jim_Knicely

Yup, the function is in 8.1.x:

dbadmin=> SELECT version();
               version
-------------------------------------
 Vertica Analytic Database v8.1.1-25
(1 row)

dbadmin=> \df lead_storage_oid
                          List of functions
  procedure_name  | procedure_return_type | procedure_argument_types
------------------+-----------------------+--------------------------
 lead_storage_oid | Integer               |
(1 row)