Check which views have rows

Question

I have a database with 300+ views, on which most of the views are empty. I need to know which views have rows. Is there a query to check which of these views have rows?

A view doesn't really have rows, it's just a stored query, so there is no equivalent of the `num_rows` you can use as a rough guide for tables. But you can just query them all and count; [this XML query](https://stackoverflow.com/a/10705088/266304) should do the trick, just change 'table' to 'view' throughout. — Alex Poole, Aug 16 '18 at 14:40

Lukasz Szozda · Accepted Answer · 2018-08-16T14:57:15.737

4

You could use:

SELECT owner, view_name, cnt              
FROM all_views, XMLTABLE('/ROWSET/ROW' passing (
             dbms_xmlgen.getxmltype(REPLACE(REPLACE(
         'select COUNT(*) AS cnt from "<owner>"."<table_name>"', '<owner>', owner)
             , '<table_name>', view_name))) COLUMNS cnt INT)
WHERE cnt > 0;

EDIT:

If we want only to check if view has rows we could exchange COUNT with EXISTS. This should also improve performance.

SELECT owner, view_name, has_rows            
FROM all_views, XMLTABLE('/ROWSET/ROW' passing (
             dbms_xmlgen.getxmltype(REPLACE(REPLACE(
              'select CASE WHEN EXISTS(SELECT 1 from "<owner>"."<table_name>") 
              THEN 1 ELSE 0 END AS has_rows FROM dual', '<owner>', owner)
             , '<table_name>', view_name))) COLUMNS has_rows INT)
WHERE has_rows > 0;

edited Aug 16 '18 at 14:57

answered Aug 16 '18 at 14:38

Lukasz Szozda

162,964
23
234
275

Good answer, but in my database, that would (A) take forever and (B) give misleading results because a lot of the views do not return rows unless the user's `SYS_CONTEXT()` is initialized first. – Matthew McPeak Aug 16 '18 at 14:41
You could add a `rownum` filter to the count query which might improve things a bit, if the OP only cares about non-zero counts rather than the actual number of rows returned. That doesn't help with Matthew's other point, of course... but that might not apply for the OP. – Alex Poole Aug 16 '18 at 14:42
1

@MatthewMcPeak I agree that RLS/context will change results. I could exchange COUNT(*) with EXISTS to make it faster a bit. – Lukasz Szozda Aug 16 '18 at 14:43

Matthew McPeak · Answer 2 · 2018-08-16T15:36:01.630

No, there is no way, other than query each one.

And even that is not perfect, since the query that defines a view could refer to things like USER or SYS_CONTEXT(). Or there could be row-level security (DBMS_RLS) policies on the underlying tables of a view. What that all means is that a view might not have any rows for you, but could very well have rows for another user at the exact same moment.

Bottom line, there is no way.

Check which views have rows

2 Answers2