Re: ORA-04167 Sort key too long

From: Yass Khogaly <ykhogaly_at_us.oracle.com>
Date: Fri, 26 Nov 1999 08:25:38 -0800
Message-ID: <81m8ma$n0l$1@inet16.us.oracle.com>

Error: ORA 1467
Text: sort key too long

---

---
  Cause:
  Action:

  Explanation:

      Any SORT (e.g. from ORDER BY , GROUP BY) must use a key that fits into a single oracle block. For the operation being performed the sort

      key was too long. From 7.3 onwards Sorts allow long sort keys but there are still restrictions for GROUP BY operations.

  Diagnosis:

1. There are several places where this error can be raised. To determine which form of the error is being signaled an errorstack should help: alter session set events '1467 trace name errorstack level 3';
   • If this shows the 1467 to be signaled in <Function:RSOALO> then there is an internal buffer (about 64K in size in Oracle8) which is being exceeded. This buffer is used for MAX / MIN type operations and can be hit is using many MAX / MIN operations on long VARCHAR2 columns.
     • Take care of string concatenations and string functions such as DECODE which may give long maximum lengths for strings. Wrap any such functions in SUBSTR() to limit the return length to see if this solves the problem.
2. Execution time issues affected sort key length:
   • Check where the sort is occurring using an explain plan of the offending statement (E.g.: Using tkprof. See [NOTE:29012.1] in Oracle Metalink)

        This should show some sort operation - even if it is just an Oracle SORT-MERGE JOIN operation. (For SMJ all columns returned for the

        tables being merged form the sort key so it is easy to get this error)

• Try to determine the columns making up the sort key and restrict the size of this sort key. The error is raised at run-time but the maximum possible lengths can be deduced from the statement itself.
• Note: If this occurs on one DB but is fine for identical tables and statement on a different machine it is possible the DB block size on the failing DB is smaller hence allowing a smaller sort key.
• See Bug:169643. RDBMS sort code cannot handle varchar columns that exceed the size of a database block. In this case, the problem is not with one of the key columns in the order by expression, it is a problem with one of the columns in the select list.

        Note that users can specify varchar(2000) columns in the select list, but the sort will terminate with an error only if one of the data values exceeds the size

        of a data block minus some overhead. In addition, this only occurs for block sizes 2K. Also, this is completely unrelated to the restrictions on LONG

        columns, as they are processed separately.

   3. Fast Full Index scan can read corrupt index blocks that are not in the B*Tree. You can check this by checking the explain plan for a Fast Full Scan. You can

       also set 1467 errorstack Level 3 and checkout the index block that should be there. This will be corrupted. See [BUG:622603] Base [BUG:487957].

"The Views expressed here are my own and not necessarily those of Oracle Corporation"

Eric DUCHET <eric.duchet_at_free.fr> wrote in message news:Ibx%3.2378$Ey.11132086_at_nnrp1.proxad.net...
> Hi,
>
> I try to create an index on a non unique field VARCHAR2(13)
> and i've got the error message :
>
> create index DI_EVT_3 on EVENT(EVT_ITEMCODE) tablespace INDEX_1;
> ERROR at line 1:
> ORA-01467: sort key too long
>
> I have 1 000 000 rows in my table.
>
> What does mean this error ?
> Haow can I do to create my index ????
>
> Thanx
>
>
Received on Fri Nov 26 1999 - 10:25:38 CST