Count Duplicates By Partition

Trying to get count of duplicates with wildcard for partition_name column, something like..

select count(pk1)
    ,partition_name
     from AA
      partition ('*')
       group by primary-key
      having count (primary_key) > 1;
What should I use as wildcard instead of that '*' ?
xoxomosAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
SELECT partition_name,
       TO_NUMBER(
           EXTRACTVALUE(
               DBMS_XMLGEN.getxmltype(
                      'select count(pk) x from (
                      select pk,count(*) from '
                   || table_name
                   || ' partition ('
                   || partition_name
                   || ') group by pk having count(*) > 1)'),
               '/ROWSET/ROW/X'))
           cnt
  FROM user_tab_partitions
 WHERE table_name = 'AA';
0
 
awking00Commented:
select p.partition_name, count(aa.pk)
from aa, user_tab_partitions p
where p.table_name = 'AA'
group by p.partition_name
having count(aa.pk) > 1;
0
 
sdstuberCommented:
http:#a38770373

will simply return the total of of the entire table for every partition

not the count "per" partition
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
sdstuberCommented:
try this...


SELECT partition_name,
       TO_NUMBER(
           EXTRACTVALUE(
               DBMS_XMLGEN.getxmltype(
                      'select count(*) X from '
                   || table_name
                   || ' partition ('
                   || partition_name
                   || ')'
               ),
               '/ROWSET/ROW/X'
           )
       )
           COUNT
  FROM user_tab_partitions
 WHERE table_name = 'AA';
0
 
sdstuberCommented:
if you only need an estimate,  you could simply look in dba_tab_partitions/all_tab_partitions/user_tab_partitions  for the NUM_ROWS column.

It will only be as accurate as your most recent statistics collection though.
0
 
xoxomosAuthor Commented:
That's right awking00.
0
 
awking00Commented:
Unfortunately, I realized that after testing.
0
 
xoxomosAuthor Commented:
Connected.
SQL> SELECT partition_name,
  2         TO_NUMBER(
  3             EXTRACTVALUE(
  4                 DBMS_XMLGEN.getxmltype(
  5                        'select count(*) X from '
  6                     || activity_accumulator          
  7                     || ' partition ('
  8                     || partition_name
  9                     || ')'
 10                 ),
 11                 '/ROWSET/ROW/X'
 12             )
 13         )
 14             COUNT
 15    FROM user_tab_partitions
 16   WHERE table_name = 'AA';
                   || activity_accumulator
                      *
ERROR at line 6:
ORA-00904: "ACTIVITY_ACCUMULATOR": invalid identifier
0
 
sdstuberCommented:
|| activity_accumulator          


don't put the table name in there
use

|| table_name


as posted
0
 
xoxomosAuthor Commented:
But is this giving me the DUPLICATES in each partition?


SQL> SELECT partition_name,
  2         TO_NUMBER(
  3             EXTRACTVALUE(
  4                 DBMS_XMLGEN.getxmltype(
  5                        'select count(*) X from '
  6                     || table_name
  7                     || ' partition ('
  8                     || partition_name
  9                     || ')'
 10                 ),
 11                 '/ROWSET/ROW/X'
 12             )
 13         )
 14             COUNT
 15    FROM user_tab_partitions
 16   WHERE table_name = 'AA';


PARTITION_NAME                      COUNT
------------------------------ ----------
AA_2010_03                              0
AA_2010_04                              0
AA_2010_05                       12337650
AA_2010_06                        8550695
AA_2010_07                        5950330
AA_2010_08                        5648572
AA_2010_09                        6758760
AA_2010_10                       14212137
AA_2010_11                       11876751
AA_2010_12                        6518745
AA_2011_01                       12203561

PARTITION_NAME                      COUNT
------------------------------ ----------
AA_2011_02                       10543610
AA_2011_03                       10543463
AA_2011_04                       11264541
AA_2011_05                       11724019
0
 
xoxomosAuthor Commented:
Using either  
SQL> SELECT partition_name,
  2         TO_NUMBER(
  3             EXTRACTVALUE(
  4                 DBMS_XMLGEN.getxmltype(
  5                        'select count(pk) x from (
  6                        select pk,count(*) from '
  7                     || table_name
  8                     || ' partition ('
  9                     || partition_name
 10                     || ') group by pk having count(*) > 1)'),
 11                 '/ROWSET/ROW/X'))
 12             cnt
 13    FROM user_tab_partitions
 14   WHERE table_name = 'AA';


no rows selected


OR

SELECT partition_name,
       TO_NUMBER(
           EXTRACTVALUE(
               DBMS_XMLGEN.getxmltype(
                      'select count(pk) x from (
                      select pk,count(*) from '
                   || table_name
                   || ' partition ('
                   || partition_name
                   || ') group by pk having count(pk1) > 1)'),
               '/ROWSET/ROW/X'))
           cnt
  FROM user_tab_partitions
 WHERE table_name = 'AA';

I get no rows.


Using below I get 19.6 million rows

SQL> select count(pk1)
  2  from bb_bb60_stats.aa
  3   group by pk1
  4   having count (pk1) > 1;
0
 
sdstuberCommented:
works for me
ee.txt
0
 
xoxomosAuthor Commented:
SQL> SELECT partition_name,
  2         TO_NUMBER(
  3             EXTRACTVALUE(
  4                 DBMS_XMLGEN.getxmltype(
  5                        'select count(pk1) x from (
  6                        select pk1,count(*) from '
  7                     || table_name
  8                     || ' partition ('
  9                     || partition_name
 10                     || ') group by pk1 having count(*) > 1)'),
 11                 '/ROWSET/ROW/X'))
 12             cnt
 13    FROM user_tab_partitions
 14   WHERE table_name = 'AA';
/e
PARTITION_NAME                        CNT
------------------------------ ----------
AA_2010_03                              0
AA_2010_04                              0
AA_2010_05                              0
AA_2010_06                              0
AA_2010_07                              0
AA_2010_08                              0
AA_2010_09                              0
AA_2010_10                              0
AA_2010_11                              0
AA_2010_12                              0
AA_2011_01                              0

PARTITION_NAME                        CNT
------------------------------ ----------
AA_2011_02                              0
AA_2011_03                              0
AA_2011_04                              0
AA_2011_05                              0
AA_2011_06                              0
AA_2011_07                              0
AA_2011_08                              0
AA_2011_09                              0
AA_2011_10                              0
AA_2011_11                              0
AA_2011_12                              0

PARTITION_NAME                        CNT
------------------------------ ----------
AA_2012_01                              0
AA_2012_02                              0
AA_2012_03                              0
AA_2012_04                              0
AA_2012_05                              0
AA_2012_06                              0
AA_2012_07                              0
AA_2012_08                              


But if i use
select count(pk1)
  ,partition_name
   from bb_bb60_stats.aa
   partition (AA_2012_06 )
   group by pk1
  having count (pk1) > 1;
~
I will get 19.6 million rows .  This is the partition where all the duplicates got in.
0
 
sdstuberCommented:
please provide some data from your table that indicates the counts are wrong
0
 
xoxomosAuthor Commented:
Profuse apologies.  Once I determined that was the partition with the duplicates, I truncated it BEFORE i ran your script :-(
0
 
xoxomosAuthor Commented:
Mil gracias
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.