Sort key too long ORA-1467

[mk83]

Hi guys,

I have an oracle8i 8.1.7.3.0 database, the one table CEPRINT has a duplicate primary key in it, causing the validate structure cascade cascade command to fall over with a ORA-1499, I need to find and delete the duplicate keys, but when I run the following select statement :

"select a.rowid, a.* from CEPRINT a, CEPRINT b where a.MANDT=b.MANDT and a.APPLCLASS=b.APPLCLASS and a.SUBCLASS=b.SUBCLASS and a.TABNAME=b.TABNAME and a.FORM=b.FORM and a.SUBNR=b.SUBNR and a.ELNR1=b.ELNR1and a.ELNR2=b.ELNR2 and a.PRART=b.PRART and a.rowid!=b.rowid;"

I get a ORA-1467 Sort key too long, the select statement has only the primary key fields in, so as far as I can see I can't change the SQL without making it useless, so is there anyway I can make this work? Or is there a another method of getting the rowid's of duplicate keys?

Any help or suggestions appreciated!

Thanks,
MK83

 

[sem]

Can you provide CEPRINT structure as well as execution plan for this query? The common workaround is to force another execution plan or to look for a patch You may try to use exceptions table when creating UK or rewrite your query with EXISTS clause. Regards, Dima

 

[mk83]

Hi Guys,

Got it sorted, for some reason, when I drop the primary key index using, alter table CEPRINT drop primary key, then the above statement works!

I'm glad to have solved it, but do any of you know why this works? I hate just finding the answer and still haveing no clue about why/how it works!

Thanks again,
MK83

 

[rcurva]

If you want to get the records with duplicates, you can try this SQL. My query may be ugly bec I am not too creative today.

SELECT a.*
FROM CEPRINT a,
WHERE a.rowid =
(
select MAX(b.rowid)
from CEPRINT b
where a.MANDT=b.MANDT
and a.APPLCLASS=b.APPLCLASS
and a.SUBCLASS=b.SUBCLASS
and a.TABNAME=b.TABNAME
and a.FORM=b.FORM
and a.SUBNR=b.SUBNR
and a.ELNR1=b.ELNR1
and a.ELNR2=b.ELNR2
and a.PRART=b.PRART
)
AND (a.MANDT, a.APPLCLASS, a.TABNAME,
a.FORM, a.SUBNR, a.ELNR1, a.ELNR2, a.PRART) IN
(
SELECT c.MANDT, c.APPLCLASS, c.TABNAME,
c.FORM, c.SUBNR, c.ELNR1, c.ELNR2, c.PRART
FROM CEPRINT c
HAVING COUNT(*) > 1
GROUP BY c.MANDT, c.APPLCLASS, c.TABNAME,
c.FORM, c.SUBNR, c.ELNR1, c.ELNR2, c.PRART
);

For ORA-1499, it is recommended to recreate the object if it is not a bug.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[mk83]

Hi sem,

How would you provide a structure and execution plan? I have never really done much SQL before? What/How does the exists clause work?

Thanks very much for the help,
MK83

 

[sem]

This error may be raised if trying to sort by long field set (not of LONG datatype, just with large LENGTHs or GREATE NUMBER of fields). In most cases it's internal sorting, caused by some joins. Increasing block size may also help. Regards, Dima