Home » SQL & PL/SQL » SQL & PL/SQL » Count of Data in Each Subpartition (Oracle 11g)
Count of Data in Each Subpartition [message #656438] Wed, 05 October 2016 23:54 Go to next message
abhayman
Messages: 29
Registered: August 2011
Location: CA
Junior 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 #656442 is a reply to message #656438] Thu, 06 October 2016 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select partition_name, subpartition_name,
...
 10  order by 1, 2
 11  /
PARTITION_NAME                 SUBPARTITION_NAME              ROWS_IN_SUBPARTITION
------------------------------ ------------------------------ --------------------
P0                             P0_DAY_MON                                        0
P0                             P0_DAY_OTHERS                                     0
P0                             P0_DAY_TUE                                        0
P0                             P0_DAY_WED                                        0
SYS_P25                        SYS_SUBP21                                        0
SYS_P25                        SYS_SUBP22                                        0
SYS_P25                        SYS_SUBP23                                        0
SYS_P25                        SYS_SUBP24                                        0
SYS_P30                        SYS_SUBP26                                        0
SYS_P30                        SYS_SUBP27                                        0
SYS_P30                        SYS_SUBP28                                        0
SYS_P30                        SYS_SUBP29                                        0

But as you never feedback in your previous topics answering our questions, telling us the solution you found and thank people who spent time to help you, this is the only thing I will post or you unless you fix your current situation.

Re: Count of Data in Each Subpartition [message #656444 is a reply to message #656442] Thu, 06 October 2016 01:25 Go to previous messageGo to next message
abhayman
Messages: 29
Registered: August 2011
Location: CA
Junior Member
That's ok .. I will write it myself .. Also, I have always sent the personal note to people for their effort ...
Re: Count of Data in Each Subpartition [message #656445 is a reply to message #656444] Thu, 06 October 2016 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You now add lie to your bad behavior.

Re: Count of Data in Each Subpartition [message #656448 is a reply to message #656445] Thu, 06 October 2016 03:37 Go to previous messageGo to next message
abhayman
Messages: 29
Registered: August 2011
Location: CA
Junior Member
FYI .. please refer to the attachment.
  • Attachment: orafaq.png
    (Size: 127.62KB, Downloaded 115 times)
Re: Count of Data in Each Subpartition [message #656449 is a reply to message #656445] Thu, 06 October 2016 03:37 Go to previous messageGo to next message
abhayman
Messages: 29
Registered: August 2011
Location: CA
Junior Member
FYI .. please refer to the attachment./forum/fa/13277/0/
Re: Count of Data in Each Subpartition [message #656450 is a reply to message #656445] Thu, 06 October 2016 03:38 Go to previous messageGo to next message
abhayman
Messages: 29
Registered: August 2011
Location: CA
Junior Member
FYI .. please refer to the attachment./forum/fa/13277/0/
Re: Count of Data in Each Subpartition [message #656451 is a reply to message #656450] Thu, 06 October 2016 03:40 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, change in my post "never" to "almost never".

Re: Count of Data in Each Subpartition [message #656452 is a reply to message #656451] Thu, 06 October 2016 03:43 Go to previous messageGo to next message
abhayman
Messages: 29
Registered: August 2011
Location: CA
Junior Member
I can post similarly for many post.. But that wont be any solution ... Anyways thanks for your help in past .. Since, you are moderator .please let me know if this question can be deleted ... Doesn't make sense to keep post of pointing fingers .. anyways I have achieved the result which I was looking for ...
Re: Count of Data in Each Subpartition [message #656467 is a reply to message #656452] Thu, 06 October 2016 13:15 Go to previous message
Bill B
Messages: 1718
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;
Previous Topic: view and Cursor behavior :Run time Exception:ORA-00932: inconsistent datatypes: expected - got -
Next Topic: Displaying DESC_TAB parms
Goto Forum:
  


Current Time: Thu Feb 22 19:43:42 CST 2018

Total time taken to generate the page: 0.08677 seconds