Re: Showstopper ORA-01467: sort key too long - PLEAAASEEE HEEELP

Hi Konstantin,

Eric had test database, so there were no problems to recreate it with another block size.

Later I had private correspondence with him. The real problem is not with group by clause of his view. I tested his definitions by myself and found that problem is with too many (60) expressions in his view like

max(DECODE( zsp_spalte, 560 , wert ,''))

Wert column is defined as varchar(2000). I proposed to define it as varchar2(1000) or to use expressions like

max(DECODE( zsp_spalte, 560 , substr(wert,1,1000) ,''))

Both solutions work fine.

Andrew Protasov

> Hi Andrew!
> Andrew Protasov <oracle_at_protasov.kiev.ua> wrote:
>
>
> > Original problem was with too complex group by clause.
> > This is from SQL Reference:
>
> > The total number of bytes in all expressions in the GROUP BY
> > clause is limited to the size of a data block minus
> > some overhead. This size is specified by the initialization
> > parameter DB_BLOCK_SIZE.
>
> What will you suggest in case of creating index on the
> fairy large (600M) table. The total length of (3) indexed
> fields is less than 60bytes. Attempt to create
> an index unexpectedly failes with ORA-01467.
> Renaming table and 'recreting as select' solved the problem.
> Oracle support is still 'looking into it'.
> When I find out that I need to recreate index
> next night should I be prepared for increasing
> DB_BLOCK_SIZE on a 15G database of the OLTP system (23.99x7)?
>
>
> --
> Sincerely Yours, Konstantin Kivi, Russia, konst_at_sirena.rinet.ru
> aka <k-kivi_at_usa.net>, 2:5020/457.24_at_fidonet.org
>
>
>
>
>
Received on Tue Nov 10 1998 - 14:02:13 CST