asked on

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 '*' ?

awking00

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;

Sean Stuber

http:#a38770373

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

not the count "per" partition

Sean Stuber

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';

Sean Stuber

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.

xoxomos

ASKER

That's right awking00.

awking00

Unfortunately, I realized that after testing.

xoxomos

ASKER

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

Sean Stuber

|| activity_accumulator

don't put the table name in there
use

|| table_name

as posted

xoxomos

ASKER

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

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xoxomos

ASKER

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;

Sean Stuber

works for me
ee.txt

xoxomos

ASKER

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.

Sean Stuber

please provide some data from your table that indicates the counts are wrong

xoxomos

ASKER

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

xoxomos

ASKER

Mil gracias