ORA-01467 sort key too long tips

 

Question:

I am getting error below:

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SMART' , tabname => 'AGENT',cascade => true, estimate_percent => 10,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 1);
BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SMART' , tabname => 'AGENT',cascade => true, estimate_percent => 10,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 1); END;

*
ERROR at line 1:
ORA-01467: sort key too long
ORA-06512: at "SYS.DBMS_STATS", line 13182
ORA-06512: at "SYS.DBMS_STATS", line 13202
ORA-06512: at line 1

My database is oracle release 10.2.0.3.0.

I looked in MOSC and there is one parameter, cursor_sharing, which needs to be exact, but in 10g the parameter is already set to EXACT.

Can you help me to resolve this error?

 

Answer:

The Oracle documentation notes the following about ORA-01467:

ORA-01467 sort key too long

Cause: A DISTINCT, GROUP BY, ORDER BY, or SET operation requires a sort key longer than that supported by Oracle. Either too many columns or too many group functions were specified in the SELECT statement.

Action: Reduce the number of columns or group functions involved in the operation.

 

In the above example, this problem occurred because of One Function based index. The user changed index from 1 to 0 and it resolved the issue.

Before the error script was-
create index abc_idx on table_name (col_name,1,10)

Here's the modified script-
create index abc_idx on table_name (col_name,0,10)

This ORA message can be thrown due to a bug in Oracle.  See MOSC for more information.

Also, depending on the tools and circumstance used, it "May be your grouping data & the single record of the group set exceeds the size of one Block…"  In this case increase the block size of temporary tablespace.

 

Also see Tom Kyte's notes here.

---