Uniquely Weird - duplicate rows

[Koivu, Lisa]

Hello all,

 

8.1.6.2 on HP/UX 11.0

 

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.

 

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'

 

 

 

  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') > 1
SQL> /

 

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)

 

 

 

 

 

Lisa Rutland Koivu Oracle Database Administrator Certified Self-Important Database Deity Slayer of Unix Administrators Wanton Kickboxing Goddess [EMAIL PROTECTED] 4850 N.State Road 7, G104 Ft. Lauderdale, FL  33319 Phone: 954-484-3191 x 138 Fax:     954-484-2933 www.neom.com www.paperclick.com www.qode.com