Solved

Count Duplicates By Partition

Posted on 2013-01-12
16
376 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
  • 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 73

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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

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 73

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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
null value 15 102
help on oracle query 5 44
how to trim oracle sql sentence in unix 17 53
Oracle Forms 10g: How-To Prevent a Commit of 1 Record in Master-Detail block. 3 26
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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

772 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