OraFAQ Forum: SQL & PL/SQL » Count of Data in Each Subpartition

Home » SQL & PL/SQL » SQL & PL/SQL » Count of Data in Each Subpartition (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Count of Data in Each Subpartition [message #656442 is a reply to message #656438]

Thu, 06 October 2016 00:59

Michel Cadot
Messages: 68647
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.

Report message to a moderator

Re: Count of Data in Each Subpartition [message #656444 is a reply to message #656442]

Thu, 06 October 2016 01:25

abhayman
Messages: 37
Registered: August 2011
Location: CA

Member

That's ok .. I will write it myself .. Also, I have always sent the personal note to people for their effort ...

Report message to a moderator

Re: Count of Data in Each Subpartition [message #656445 is a reply to message #656444]

Thu, 06 October 2016 01:29

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You now add lie to your bad behavior.

Report message to a moderator

Re: Count of Data in Each Subpartition [message #656448 is a reply to message #656445]

Thu, 06 October 2016 03:37

abhayman
Messages: 37
Registered: August 2011
Location: CA

Member

FYI .. please refer to the attachment.

Attachment: orafaq.png
(Size: 127.62KB, Downloaded 1440 times)

Report message to a moderator

Re: Count of Data in Each Subpartition [message #656449 is a reply to message #656445]

Thu, 06 October 2016 03:37

abhayman
Messages: 37
Registered: August 2011
Location: CA

Member

FYI .. please refer to the attachment. /forum/fa/13277/0/

Report message to a moderator

Re: Count of Data in Each Subpartition [message #656450 is a reply to message #656445]

Thu, 06 October 2016 03:38

abhayman
Messages: 37
Registered: August 2011
Location: CA

Member

FYI .. please refer to the attachment. /forum/fa/13277/0/

Report message to a moderator

Re: Count of Data in Each Subpartition [message #656451 is a reply to message #656450]

Thu, 06 October 2016 03:40

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Count of Data in Each Subpartition [message #656452 is a reply to message #656451]

Thu, 06 October 2016 03:43

abhayman
Messages: 37
Registered: August 2011
Location: CA

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 ...

Report message to a moderator

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;

Report message to a moderator

Previous Topic:	view and Cursor behavior :Run time Exception:ORA-00932: inconsistent datatypes: expected - got -
Next Topic:	Displaying DESC_TAB parms

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 16:45:18 CDT 2024