Count of Data in Each Subpartition [message #656438] |
Wed, 05 October 2016 23:54 |
|
abhayman
Messages: 37 Registered: August 2011 Location: CA
|
Member |
|
|
Hi,
I have a table with 256 subpartition ( Part_1 , Part_2 ...)
I am able to get the count each subpartition wise .
select count(1) from emp subpartition (PART_1) .
But I am unable to get count each subpartition wise in a single query.
Like :--
PART_1 :-- 4500
PART 2 :-- 3000
The table is not frequently analyzed so I am not sure if dba_objects will have the latest count.
Regards
|
|
|
|
|
|
|
|
|
|
|
Re: Count of Data in Each Subpartition [message #656467 is a reply to message #656452] |
Thu, 06 October 2016 13:15 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Since you really want your partitioned table to have fresh statistics anyway, you can do the following. After the statistics are gathered the num_rows will be correct. In a cost based optimizer, always have reasonable statistics. personally I run a gather statistics on the entire database every night at 1am.
EXEC dbms_stats.delete_table_stats(USER, 'EMP')
EXEC dbms_stats.gather_table_stats(USER, 'EMP', GRANULARITY => 'SUBPARTITION');
SELECT Table_name,
Partition_name,
Global_stats,
Last_analyzed,
Num_rows
FROM User_tab_partitions
WHERE Table_name = 'EMP'
ORDER BY 1, 2, 4 DESC NULLS LAST;
|
|
|