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

From: Andrew Protasov <oracle_at_protasov.kiev.ua>
Date: Fri, 6 Nov 98 22:56:12 +0200
Message-ID: <ABi9sGsK42@protasov.kiev.ua>

Hi Satar,

Sometimes things are obvious only for me. Ok, let it be details:

SVRMGR> drop table test;
Statement processed.

SVRMGR> create table test
     2> (x varchar2(4000)
     3> );

Statement processed.
SVRMGR> insert into test values(rpad('1',4000,'1')); 1 row processed.
SVRMGR> select count(*) from test group by x; COUNT(*)

---

         1
1 row selected.
SVRMGR> select count(*) from test group by x,x; COUNT(*)

---

ORA-01467: sort key too long
SVRMGR> spool off

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.

Is it obvious now, why it is necessary to recreate database with more large block size?

Andrew Protasov

> Andrew,
> What does Block Size have to do with anything? And where did you get the
> value of 4k or 8k? Besides, changing the block size, means recreating the
> database. You are offerring drastic advise, why replace the car when the tire
> has a flat...and are we sure that the car is the reason for the flat?
>
> Satar
>
> In article <ABtWeGsK42_at_protasov.kiev.ua>,
> oracle_at_protasov.kiev.ua wrote:
> > Hi,
> >
> > What is block size of your database? Is it 2k? It is too small.
> > You should increase it. Export full, recreate database with
> > 4k or 8k block size and then import full.
> >
> > Andrew Protasov
> >
> > > Hi,
> > > I'm still porting from Sqlanywhere to ORACLE 7.3.3 under NT 4.0.
> > > Now, it looks I have a showstopper.
> > >
> > > I've a view which look likes this:
>
>
> --
> Oracle DBA/UNIX System Admin
> Advanced Enterprise Solutions
> (949) 756-0588
> Oracle Re-Seller
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
>
Received on Fri Nov 06 1998 - 14:56:12 CST