Null rows being displayed after partitioning a table with Data

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.

Issue/Behavior:
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;

Cause:

Statistics must be missing

Resolution:

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);

Advertisements
This entry was posted in Oracle Database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s