Solved

Count Duplicates By Partition

Posted on 2013-01-12
16
379 Views
Last Modified: 2013-01-16
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 '*' ?
0
Comment
Question by:xoxomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 2
16 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 38770373
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 38770427
http:#a38770373

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

not the count "per" partition
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38770443
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 38770445
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
 

Author Comment

by:xoxomos
ID: 38770450
That's right awking00.
0
 
LVL 32

Expert Comment

by:awking00
ID: 38770451
Unfortunately, I realized that after testing.
0
 

Author Comment

by:xoxomos
ID: 38770475
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 38770480
|| activity_accumulator          


don't put the table name in there
use

|| table_name


as posted
0
 

Author Comment

by:xoxomos
ID: 38775148
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 300 total points
ID: 38775211
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
 

Author Comment

by:xoxomos
ID: 38775396
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 38775639
works for me
ee.txt
0
 

Author Comment

by:xoxomos
ID: 38785245
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 38785279
please provide some data from your table that indicates the counts are wrong
0
 

Author Comment

by:xoxomos
ID: 38785336
Profuse apologies.  Once I determined that was the partition with the duplicates, I truncated it BEFORE i ran your script :-(
0
 

Author Closing Comment

by:xoxomos
ID: 38785338
Mil gracias
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question