The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

List Table Rows by ROS Container

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!

Comments

  • Hello Jim,

    Is it also available for v8.1.x ?

    Regards

  • Jim_KnicelyJim_Knicely Administrator

    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)
    
Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.