CODEWITHSUNDEEP
sqloracle-databaseplsqldatabase-partitioning
tefached616
tefached616

Reputation: 53

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

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

Upvotes: 1

Views: 1218

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59455

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.

Upvotes: 1

GMB
GMB

Reputation: 222482

This method using dynamic SQL is heavy to implement, and slow to execute (since you need to manually count rows in each partition). Also, you now find yourself in need of some additional logic, that will require more PL/SQL code.

System view user_tab_partitions has column called num_rows that is documented as: Number of rows in the partition. The reliability of the information it contains depends on the freshness of your statistics.

So, if your statistics are up to date, then you can get the information that you are looking for directly from the view:

select table_name, partition_name
from user_tab_partitions
where num_rows = 0
order by table_name, partition_name

Upvotes: 0

Related Questions