Useful Catalog Queries

Copy-paste examples of how to utilize metadata in virtual catalogs with SQL

The catalog tables allow for some useful information to be extracted from the database. See Virtual Catalogs for a complete listing.


Table Resource Usage

Table RAM Usage 
1
2
3
4
5
6

SELECT IF(GROUPING(source_rank) = 1,'Total',STRING(source_rank)) AS "Rank", SUM(size) AS "Bytes"
FROM ki_tiered_objects
WHERE tier = 'RAM'
AND outer_object = '[<schema name>.]<table name>'
GROUP BY ROLLUP(source_rank)
ORDER BY INT(source_rank) NULLS LAST
Table Disk Usage 
1
2
3
4
5
6
7

SELECT IF(GROUPING(source_rank) = 1,'Total',STRING(source_rank)) AS "Rank", SUM(size) AS "Bytes"
FROM ki_tiered_objects
WHERE tier = 'PERSIST'
AND outer_object = '[<schema name>.]<table name>'
AND id NOT LIKE 'Wal%'
GROUP BY ROLLUP(source_rank)
ORDER BY INT(source_rank) NULLS LAST

Materialized View Dependencies

Specified Materialized View Dependencies 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
    child.schema_name||'.'||child.object_name AS child_name,
    d.dep_obj_kind,
    parent.schema_name||'.'||parent.object_name AS parent_name,
    d.src_obj_kind
FROM ki_depend d
JOIN ki_objects root ON root.oid = mv_oid AND
    schema_name = '<mv_schema_name>' AND
    object_name = '<mv_name>'
JOIN ki_objects parent ON parent.oid = src_obj_oid
JOIN ki_objects child ON child.oid = dep_obj_oid
ORDER BY 1, 3
All Materialized View Dependencies 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    root.schema_name || '.' || root.object_name AS mv_name,
    child.schema_name||'.'||child.object_name AS child_name,
    d.dep_obj_kind,
    parent.schema_name||'.'||parent.object_name AS parent_name,
    d.src_obj_kind
FROM ki_depend d
JOIN ki_objects root ON root.oid = mv_oid
JOIN ki_objects parent ON parent.oid = src_obj_oid
JOIN ki_objects child ON child.oid = dep_obj_oid
ORDER BY 1, 2, 3