Re: ORA-01467: sort key too long

Keith Boulton wrote:

> On Mon, 26 Jan 1998 18:14:07 -0500, Tansel Ozkan <tansel_at_openix.com>
> wrote:
>
> >Hello everyone,
> >
> >I am having a strange problem. A query that I am running on our
> >production database is giving me "ORA-01467: sort key too long" error
> >message. One of the selected fields is datatype varchar2(2000). The
> >query returns 20 records with the varchar2(2000) field occupying full
> >2000 characters. To create the same environment on the development
> >database, I inserted new records such that for a given where condition
> >the query returns about 20 records with that field occupying 2000
> >characters. However, when we run the same query on the development
> >database, we are not getting this error message. Any ideas??
> >
> >Thanks..
>
> I read in an oracle manual (can't remember which) the other day, that
> the sort key has to fit in a single database block. If your blocksize
> is 2k then this may explain the problem.
>
> It may be that the block size is different in your test and production
> databases.
>
> It may be that this restriction applies only when the sort requires
> sorts to disk and that the sort_area_size is set differently in the
> two databases.

  Thank you very much Keith.

Yes, you are right. The production database has a db_block_size of 2K while the development has 4K.

However, one thing I still don't understand is that this large field is not

part of the sort key. What is meant by sort key? Isn't it the column by which the records should be sorted? Received on Tue Jan 27 1998 - 00:00:00 CST