Null rows being displayed after partitioning a table with Data
Partitioning in a Data Warehouse kind of environment is very common. In the recent times, I’ve observed a behavior with the Partitioned tables in Oracle, which I wanted to share with you today.
The Table size was over 80G in Database, and I had created a new Partitioned Table with partitioning and Sub-partitioning on it. After the partitioning is done, the data was loaded from the original table, the data loaded very well with no issue. But, after the Data was imported; I tried to check the Number of Rows in each Partition/SubPartition and it was displaying “NULL” instead of a number
SELECT table_name, tablespace_name, partitioned, num_rows
FROM user_tables where table_name=XXXXX;
Statistics must be missing
Gathered Statistics on the table, which resolved the issue
exec dbms_stats.gather_table_stats(OWNNAME=><TABLE_OWNER>, TABNAME=><TABLE_NAME>,DEGREE=> 4, CASCADE=> TRUE,ESTIMATE_PERCENT=>50);