Message ORA-01467 while rebuild an index or drop/create an index

[LUCAS_7]

On Oracle 7.3.4 (HP-UX 10.20), we tried to rebuild an index (quite big : initial 40M next 40M with two columns VARCHAR2(16) and VARCHR2(10) on a 3 500 000 lines table)
We have ORA-01467 sort key too long message
The index is not a new index, it exists since several years...
What happens ?

[twang]

To workaround this error, you may have to rebuild your DB with a larger block size.

[twang]

As I remember this is a bug and has been corrected in patchset 7.3.4.3 and Oracle8.
Do you try to drop and recreate the index instead of rebuild?

[Yogeeraj_1]

hi,

to add to Mr. Twang's reply above:

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76999/e900.htm#1656

and search for 1467
============================================
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.
============================================

There is a limit on the memory, occupied by agrerate functions :
"the group-by expression and all of the non-distinct aggregates (e.g., sum,avg) need to fit within a single database block (actually, a fraction of the block)."


You can try doing a SUBSTR on the columns -- well, as long as cursor_sharing was exact (so the db knows how big the substr is)

hope this helps!

regards
Yogeeraj

[LUCAS_7]

We tried to drop and create the index : same error.
On a development machine with the same Oracle version and the same data, no error (rebuild OK, drop/create OK).
We want to try :
- exp table A
- truncate table A
- drop index I_A
- imp table A indexes=no
- create I_A
Maybe it works...

[LUCAS_7]

It's ok with the development server (HP UX 10.20A) and bad with HP UX 10.20C !!