Solved

Count Duplicates By Partition

Posted on 2013-01-12
16
377 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 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
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 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

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

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

821 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