Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Count Duplicates By Partition

Posted on 2013-01-12
16
Medium Priority
?
388 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

579 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