Uniquely Weird - duplicate rows

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Tue, 01 May 2001 12:36:53 -0700
Message-ID: <F001.002F6ADE.20010501123025@fatcity.com>

Hello all,

<SPAN

class=870322819-01052001>
8.1.6.2 on HP/UX
11.0
<SPAN

class=870322819-01052001>
Has anyone seen
something like this before? I can't rebuild the primary key - the error is duplicate rows found. So I check the data (first listing) and see duplicate rows via fts. When I give it a hint to use the primary key it shows nothing. When I dump the rowid's, there are no dups. Weirdness. I think I'll have to drop the index and create it from scratch.
<SPAN

class=870322819-01052001>
SQL> select
partner_id, category_id, relationship_type, count('x') 2 from qode$partner_category_sold 3 group by partner_id, category_id, relationship_type 4 having count('x') > 1;

PARTNER_ID
CATEGORY_ID RE COUNT('X')---------- ----------- --

----------    
736844        2921 
VE          2    
736844        2922 
VE          2    
736844        3158 
VE          2    
736844        3160 
VE          2    
736844        3161 
VE          2    
736844        3162 
VE          2    
736844        3163 
VE          2    
736844        3164 
VE          2    
736844        3166 
VE          2    
736844        3233 
VE          2    
736844        3235 
VE          2    
736844        3249 
VE          2    
736844        3253 
VE          2    
736844        3255 
VE          2    
736844        3258 
VE          2    
736844        3262 
VE          2    
736844        3264 
VE          2    
736844        3273 
VE          2    
736844        3283 
VE          2    
736844        3297 
VE          2    
736844        3303 
VE          2

21 rows
selected.

Execution

Plan----------------------------------------------------------

0 SELECT STATEMENT

Optimizer=CHOOSE   1    0   
FILTER   2    1     SORT (GROUP 
BY)   3    2

TABLE ACCESS (FULL) OF 'PARTNER_CATEGORY_SOLD'
<SPAN

class=870322819-01052001>
<SPAN

class=870322819-01052001>
<SPAN

class=870322819-01052001>
1
select /*+ index (pcs xpkpartner_category_sold) */ 2 partner_id, category_id, relationship_type, count('x') 3 from qode$partner_category_sold pcs 4 group by partner_id, category_id, relationship_type 5* having count('x') > 1SQL> /

no rows
selected

Execution

Plan----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=144492 Bytes
=4334760)

   
1    0   FILTER   2    
1     SORT (GROUP BY NOSORT) (Cost=26 Card=144492

Bytes=433476
0)


3    2       INDEX (FULL SCAN) OF
'XPKPARTNER_CATEGORY_SOLD'
(UNIQU          E) (Cost=26
Card=144492 Bytes=4334760)

<SPAN

class=870322819-01052001>
<SPAN

class=870322819-01052001>
<SPAN

class=870322819-01052001>
<SPAN

class=870322819-01052001>

<TABLE border=0 cellPadding=0 cellSpacing=0
style="mso-cellspacing: 0in; mso-padding-alt: 0in 0in 0in 0in">

      
      
      <SPAN 
      style="COLOR: navy; FONT-FAMILY: 'Monotype Corsiva'; FONT-SIZE: 20pt; mso-bidi-font-size: 12.0pt">Lisa 
      Rutland Koivu<SPAN 
      style="COLOR: navy; FONT-FAMILY: Verdana; FONT-SIZE: 7.5pt"><SPAN 
      style="COLOR: navy; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Oracle Database 
      Administrator<?xml:namespace prefix = o ns = 
      "urn:schemas-microsoft-com:office:office" />
      <SPAN 
      style="COLOR: navy; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Certified 
      Self-Important Database Deity
      <SPAN 
      style="COLOR: navy; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Slayer of Unix 
      Administrators
      <SPAN 
      style="COLOR: navy; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Wanton 
      Kickboxing Goddess
      <SPAN 
      style="COLOR: navy; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">lkoivu_at_qode.com<SPAN 
      style="COLOR: navy; FONT-FAMILY: Verdana; FONT-SIZE: 7.5pt"><IMG 
      border=0 height=20 id=_x0000_i1026 
      src="http://www.neom.com/images/email_img/1px.gif" width=1><IMG 
      alt=NeoMedia border=0 height=26 id=_x0000_i1027 
      src="http://www.neom.com/images/email_img/neomlogo.gif" width=135>4850 
      N.State Road 7, G104
      <SPAN 
      style="COLOR: navy; FONT-FAMILY: Verdana; FONT-SIZE: 7.5pt">Ft. 
      Lauderdale, FL  33319Phone: 954-484-3191 x 
      138Fax:     954-484-2933<A 
      href="http://www.neom.com/">www.neom.com<A 
      href="http://www.paperclick.com/">www.paperclick.com<A 
      href="http://www.qode.com/">www.qode.com
      <SPAN 
      style="COLOR: navy; FONT-FAMILY: Verdana; FONT-SIZE: 7.5pt"><IMG 
      border=0 height=20 id=_x0000_i1029 
      src="http://www.neom.com/images/email_img/1px.gif"

width=1>

      <A 
      href="http://www.paperclick.com/home.htm"><IMG 
      alt="P a p e r C l i c k . c o m" border=0 height=37 id=_x0000_i1030 
      src="http://www.neom.com/images/email_img/PaperclickLogoFrameW.gif" 
      width=63>

Received on Tue May 01 2001 - 14:36:53 CDT