Showstopper ORA-01467: sort key too long - PLEAAASEEE HEEELP
Eric Euerlings
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:
create or replace view
v_zeugniszelle_partner(partner,
sprache_korrespondenz,
mandant,
abteilung,
zeugnis,
zeugniszeile,
zeugnisspalte,
zeugniszelledef,
zeugnistyp,
zeugnissequenz,
zze_bez,
benutzer_erfasst,
datum_zeit_erfasst,
benutzer_geandert,
datum_zeit_geandert) as select partner.part;
create or replace view
ecoopen.v_zeugnistabelle(mandant,
abteilung,
zeugnistyp,
zeugnis,
partner,
partner_eltern,
partner_lehrfirma,
zeugnis_angebotsstruktur,
sprache,
zeile,
spalte01,
spalte02,
spalte03,
spalte04,
spalte05,
spalte06,
spalte07,
spalte08,
spalte09,
spalte10,
spalte11,
spalte12,
spalte13,
spalte14,
spalte15,
spalte16,
spalte17,
spalte18,
spalte19,
spalte20,
spalte21,
spalte22,
spalte23,
spalte24,
spalte25,
spalte26,
spalte27,
spalte28,
spalte29,
spalte30,
spalte31,
spalte32,
spalte33,
spalte34,
spalte35,
spalte36,
spalte37,
spalte38,
spalte39,
spalte40,
spalte41,
spalte42,
spalte43,
spalte44,
spalte45,
spalte46,
spalte47,
spalte48,
spalte49,
spalte50,
spalte51,
spalte52,
spalte53,
spalte54,
spalte55,
spalte56,
spalte57,
spalte58,
spalte59,
spalte60)
as select mandant,
abteilung,
zeugnistyp,
zeugnis,
partner,
partner_eltern,
partner_lehrfirma,
zeugnis_angebotsstruktur,
max(sprache),
zze_zeile,
max(DECODE( zsp_spalte, 10 , wert ,'')),
max(DECODE( zsp_spalte, 20 , wert ,'')),
max(DECODE( zsp_spalte, 30 , wert ,'')),
max(DECODE( zsp_spalte, 40 , wert ,'')),
max(DECODE( zsp_spalte, 50 , wert ,'')),
max(DECODE( zsp_spalte, 60 , wert ,'')),
max(DECODE( zsp_spalte, 70 , wert ,'')),
max(DECODE( zsp_spalte, 80 , wert ,'')),
max(DECODE( zsp_spalte, 90 , wert ,'')),
max(DECODE( zsp_spalte, 100 , wert ,'')),
max(DECODE( zsp_spalte, 110 , wert ,'')),
max(DECODE( zsp_spalte, 120 , wert ,'')),
max(DECODE( zsp_spalte, 130 , wert ,'')),
max(DECODE( zsp_spalte, 140 , wert ,'')),
max(DECODE( zsp_spalte, 150 , wert ,'')),
max(DECODE( zsp_spalte, 160 , wert ,'')),
max(DECODE( zsp_spalte, 170 , wert ,'')),
max(DECODE( zsp_spalte, 180 , wert ,'')),
max(DECODE( zsp_spalte, 190 , wert ,'')),
max(DECODE( zsp_spalte, 200 , wert ,'')),
max(DECODE( zsp_spalte, 210 , wert ,'')),
max(DECODE( zsp_spalte, 220 , wert ,'')),
max(DECODE( zsp_spalte, 230 , wert ,'')),
max(DECODE( zsp_spalte, 240 , wert ,'')),
max(DECODE( zsp_spalte, 250 , wert ,'')),
max(DECODE( zsp_spalte, 260 , wert ,'')),
max(DECODE( zsp_spalte, 270 , wert ,'')),
max(DECODE( zsp_spalte, 280 , wert ,'')),
max(DECODE( zsp_spalte, 290 , wert ,'')),
max(DECODE( zsp_spalte, 300 , wert ,'')),
max(DECODE( zsp_spalte, 310 , wert ,'')),
max(DECODE( zsp_spalte, 320 , wert ,'')),
max(DECODE( zsp_spalte, 330 , wert ,'')),
max(DECODE( zsp_spalte, 340 , wert ,'')),
max(DECODE( zsp_spalte, 350 , wert ,'')),
max(DECODE( zsp_spalte, 360 , wert ,'')),
max(DECODE( zsp_spalte, 370 , wert ,'')),
max(DECODE( zsp_spalte, 380 , wert ,'')),
max(DECODE( zsp_spalte, 390 , wert ,'')),
max(DECODE( zsp_spalte, 400 , wert ,'')),
max(DECODE( zsp_spalte, 410 , wert ,'')),
max(DECODE( zsp_spalte, 420 , wert ,'')),
max(DECODE( zsp_spalte, 430 , wert ,'')),
max(DECODE( zsp_spalte, 440 , wert ,'')),
max(DECODE( zsp_spalte, 450 , wert ,'')),
max(DECODE( zsp_spalte, 460 , wert ,'')),
max(DECODE( zsp_spalte, 470 , wert ,'')),
max(DECODE( zsp_spalte, 480 , wert ,'')),
max(DECODE( zsp_spalte, 490 , wert ,'')),
max(DECODE( zsp_spalte, 500 , wert ,'')),
max(DECODE( zsp_spalte, 510 , wert ,'')),
max(DECODE( zsp_spalte, 520 , wert ,'')),
max(DECODE( zsp_spalte, 530 , wert ,'')),
max(DECODE( zsp_spalte, 540 , wert ,'')),
max(DECODE( zsp_spalte, 550 , wert ,'')),
max(DECODE( zsp_spalte, 560 , wert ,'')),
max(DECODE( zsp_spalte, 570 , wert ,'')),
max(DECODE( zsp_spalte, 580 , wert ,'')),
max(DECODE( zsp_spalte, 590 , wert ,'')),
max(DECODE( zsp_spalte, 600 , wert ,''))
from ecoopen.workfile_zeugnis
group by
mandant,abteilung,zeugnistyp,zeugnis,partner,partner_eltern,partner_lehrfirm
a,zeugnis_angebotsstruktur,zze_zeile
;
The view will be created well, but when I select on it, I get this:
select count(*) from v_zeugnistabelle;
select count(*) from v_zeugnistabelle
*
ORA-01467: sort key too long
Pleeeeaaaassse help!!!!!
sa...@my-dejanews.com
y2k% oerr ora 01467
01467, 00000, "sort key too long"
// *Cause:
// *Action:
y2k%
ok, maybe that didn't help...try this:
Cause Action ORA–01467 sort key too long 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. Reduce the number of columns or group functions involved in
the operation.
Personally, I believe that's one hell of a view. How about if you broke it up
to two seperate views?
Satar
In article <71shem$3mc$1...@bw107zhb.bluewin.ch>,
"Eric Euerlings" <euer...@swift.ch> wrote:
> Hi,
> I'm still porting from Sqlanywhere to ORACLE 7.3.3 under NT 4.0.
> Now, it looks I have a showstopper.
>
>
> The view will be created well, but when I select on it, I get this:
>
> select count(*) from v_zeugnistabelle;
> select count(*) from v_zeugnistabelle
> *
> ORA-01467: sort key too long
>
> Pleeeeaaaassse help!!!!!
>
>
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Andrew Protasov
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.
>
Eric Euerlings
Oops,
how do I evaluate the blocksize and how do I encrease this ?
Cheers Eric
Eric Euerlings
I know that's a hell of a view. It's not my design and I have to port the
hole bunch of it to Oracle from SQL Anywhere.
Client code is not touchable so I have to port 1 to 1. What you saw was one
of the smarter sampel.
Thanks again
Eric
Eric Euerlings
I did it but have the same error,
any other hints ?
eric
Andrew Protasov wrote in message ...
sa...@my-dejanews.com
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 <ABtWe...@protasov.kiev.ua>,
ora...@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
Andrew Protasov
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
Konstantin Kivi
Andrew Protasov <ora...@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, ko...@sirena.rinet.ru
aka <k-k...@usa.net>, 2:5020/457...@fidonet.org
Andrew Protasov
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