Re: ORA01467: sort key too long error

  • From: Nigel Thomas <>
  • To: Saad Khan <saad4u@xxxxxxxxx>
  • Date: Sat, 9 Jan 2010 10:33:06 +0000


One of the reasons for large sort keys can be poor application design and/or
poor query design. For example, if you find yourself having to use SELECT
DISTINCT with a large number of columns. It may be (relationally) possible
to recast the query in such a way as to avoid the problem. To take a trivial
example, you may find that the query is something like:

select distinct
from dept d, emp e
where e.deptno = d.deptno

The query above uses a sort distinct, whereas:

from dept d
where exists (
select 1 from emp e
where e.deptno = d.deptno

doesn't need to have a sort.

Obviously your sort key is much more than a single column - but the same
principal may apply



Other related posts: