General
cancel
Showing results for 
Search instead for 
Did you mean: 

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

LUCAS_7
Occasional Contributor

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

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 ?
5 REPLIES
twang
Honored Contributor

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

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

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

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
Honored Contributor

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

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
LUCAS_7
Occasional Contributor

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

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
Occasional Contributor

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

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