Return the table_name and partition_name whose count have a value of 0

Question

I'm trying to writing a query that return the table_name and partition_name whose count of that partition have a value of 0.

I've got the below query that return the SELECT COUNT() of the tables and its partition:

SELECT 'SELECT COUNT(*) FROM ' || TABLE_NAME || ' PARTITION (' || PARTITION_NAME || ');'
FROM user_tab_partitions
ORDER BY table_name;

These are some of the SELECT that return:

SELECT COUNT(*) FROM A5109713 PARTITION (PT5109713_201210);
SELECT COUNT(*) FROM A5109713 PARTITION (PT5109713_201104);
SELECT COUNT(*) FROM A5109713 PARTITION (PT5109713_201301);

The last one it's the only that return data:

COUNT(*) |
2430276  |

What I need is a query that return the table_name and partition_name of those who has no data. Something like this:

TABLE_NAME | PARTITION_NAME
A5109713   | PT5109713_201210
A5109713   | PT5109713_201104

Answer 1

I would a procedure like this:

DECLARE
    r INTEGER;
    cur sys_refcursor;
BEGIN
    FOR aPart IN (SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS ORDER BY TABLE_NAME, PARTITION_POSITION) LOOP
        OPEN cur FOR 'SELECT ROWNUM FROM '||aPart.TABLE_NAME||' PARTITION ('||aPart.PARTITION_NAME||') WHERE ROWNUM <= 1';
        FETCH cur INTO r;
        IF cur%NOTFOUND THEN
            DBMS_OUTPUT.PUT_LINE(aPart.TABLE_NAME||CHR(9)||aPart.PARTITION_NAME);
        end if;
        close cur;
    END LOOP;
END;

I would not rely on NUM_ROWS at view USER_TAB_PARTITIONS because the reliability of the information it contains depends on the freshness of your statistics.

I use SELECT ROWNUM FROM ... WHERE ROWNUM <= 1; instead of COUNT(*) for performance reason. Actually you are not interested in the total number of rows, you just like to know whether they are greater than 0.

In worst case COUNT(*) runs a FULL-TABLE Scan which is much slower than reading only the first record.