Sort key too long.

[Thomasps]

Hi

When rebuilding very big indexes, the rebuilding failed with the error " Sort key too long".


Oracel 7.3.4
OS: IBM-AIX
sort_area_size 200MB
Index size 7GB


Appreciate any work around for this error..?


Regards


Thomas

[agasimani]

The reasons oracle says for this error are:
* any query that needs to sort a VARCHAR2(2000) column
* during a select statement
* a query with an order by or group by
* creating a view
* during export
* running a report

You will have to try the following solutions to get over this problem:
1. Increase the db_block_size.
2. Break down the query into smaller queries. Change the order by or group by statement.
3. Create and index on the columns where the sorting is happening.
4. You can try to restrict buffer allocation for the sort. You can do this by using the SUBSTR(columname, n1,n2) function. For example: substr(col, 1, 255) where col is the column the sort is erroring out on (like varchar2(2000)) will limit the buffer allocation to 255 bytes. This will not work for string concatenations.

All the best.

[Thomasps]

Hi

Thanks agasimani!!


But I am getting the error during the Index Rebuild. And the instance is in restricted mode. Nobody else is accessing except me!

Looking for a solution, so that I can rebuild my big indexes ... Which, each size around 7GB.. Any work around for this problem..?

[agasimani]

Try dropping and recreating the index instead of rebuild. I think it is a bug. By the way why dont you migrate to 8i or 9i?

[agasimani]

Yes Thomasps, it is indeed a bug 487957. The patchset for this is 7.3.4.3.

[Thomasps]

Hi

Thanks for the info. I will apply the patch and update the forum. Once again with thanks...


Thomas