Home » SQL & PL/SQL » SQL & PL/SQL » Index creation error
Index creation error [message #246829] Fri, 22 June 2007 02:31 Go to next message
ak_sunil
Messages: 2
Registered: June 2007
Location: Bangalore
Junior Member

When i run the following index creation query

CREATE INDEX SCADMIN.PROBSUMMARYM1_55
ON SCADMIN.PROBSUMMARYM1(OPEN_TIME, COMPANY,
UIS_FIELD_3, NUMBERPRGN)
STORAGE(INITIAL 10M NEXT 10M MAXEXTENTS 400)
TABLESPACE SC_INDEX ONLINE;

I get the following error

ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

The max length of all the fields included in the query do not cross
even 100.
Can anyone help me in resolving this issue
Re: Index creation error [message #246832 is a reply to message #246829] Fri, 22 June 2007 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A DESC of your table may help, as well as you Oracle version (4 decimals).

Regards
Michel
Re: Index creation error [message #246923 is a reply to message #246829] Fri, 22 June 2007 08:26 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
STORAGE clauses are legacy clauses that are no longer necessary, unless you are using an ancient version of Oracle.
Re: Index creation error [message #246998 is a reply to message #246829] Fri, 22 June 2007 15:11 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
The ONLY thing that will generate that error is if the total length of the columns in the index are greater then 3215 in length. As the other two said, run the following command and paste the results.

DESC SCADMIN.PROBSUMMARYM1
Re: Index creation error [message #247125 is a reply to message #246829] Sun, 24 June 2007 07:25 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post your db block size as well.
Re: Index creation error [message #334941 is a reply to message #246832] Fri, 18 July 2008 14:56 Go to previous messageGo to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Michel,

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production


Here is my table structure

SQL> create table t1(x varchar2(4000));

Table created.


Here is my index creation


SQL> create index t1_idx on t1(x);
create index t1_idx on t1(x)
                       *
ERROR at line 1:
ORA-01450: maximum key length (3118) exceeded


My db block size is 4K. It is running on linux OS.

I googled this issue. This issue is based on block size and max index length? Where do i find the max index length?


Re: Index creation error [message #334944 is a reply to message #246829] Fri, 18 July 2008 15:10 Go to previous message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
govindts,

why hijack a thread more than a year old?

Too bad some are incapable or unwilling to SEARCH for documented answers.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/limits003.htm


Indexes total size of indexed column 75% of the database block size minus some overhead

[Updated on: Fri, 18 July 2008 15:19] by Moderator

Report message to a moderator

Previous Topic: Cursor is Out of Scope
Next Topic: Using DECODE to determine if a date falls within a date range
Goto Forum:
  


Current Time: Fri Dec 02 14:17:38 CST 2016

Total time taken to generate the page: 0.09240 seconds