From lkoivu@qode.com Tue, 01 May 2001 12:36:53 -0700 From: "Koivu, Lisa" Date: Tue, 01 May 2001 12:36:53 -0700 Subject: Uniquely Weird - duplicate rows Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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') > 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)          
Lisa Rutland KoivuOracle Database Administrator Certified Self-Important Database Deity Slayer of Unix Administrators Wanton Kickboxing Goddess lkoivu@qode.comNeoMedia4850 N.State Road 7, G104 Ft. Lauderdale, FL  33319Phone: 954-484-3191 x 138Fax:     954-484-2933www.neom.comwww.paperclick.comwww.qode.com P a p e r C l i c k . c o m