DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: ORA-01450: maximum key length (3214) exceeded

  1. #1
    Join Date
    May 2001
    Posts
    22

    Question

    hi,

    I've a 'create index' problem ...
    I need to create an index on VARCHAR2(4000) col. but something is wrong ??

    this is what I've done (815):

    1. create table tst (a varchar2(4000));
    2. create index tst_idx on tst(a);

    message:

    ERROR at line 1:
    ORA-01450: maximum key length (...) exceeded


    I've tried on 2 differents db :

    -with 8k block:

    ERROR at line 1:
    ORA-01450: maximum key length (3218) exceeded

    -with 2k block:

    ERROR at line 1:
    ORA-01450: maximum key length (758) exceeded

    Same results , what a pity! :(

    I've found in the doc. :

    'ORA-01450 maximum key length (string) exceeded

    Cause:
    The combined length of all the columns specified in a CREATE INDEX statement exceeded the maximum index length. The maximum index length varies by operating system. The total index length is computed as the sum of the width of all indexed columns plus the number of indexed columns. Date fields have a length of 7, character fields have their defined length, and numeric fields have a length of 22. Numeric length = (precision/2) + 1. If negative, add +1.

    Action: Select columns to be indexed so the total index length does not exceed the maximum index length for the operating system. See also your operating system-specific Oracle documentation.'


    But what this exactly means ?

    1.what's the 'maximum index length' on NT and on SUN SOLARIS (I use both) in my sample ?

    2.My 'maximum index length' is it : 4000(=all indexed columns) + 1(=number of indexed columns) ?

    I don't understand what 'maximum index length' is ? :(((

    3.And, of course, what can I do to create my index on my varchar2(4000) col. ?
    I suppose I can create an index on a varchar2(4000) col, isn't it ?


    thanks in advance


  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    1-2:

    The maximum key length for your database is approximately 40% of the database block size minus some overhead.

    3:

    Recreate the DB with a larger block size.



    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    May 2001
    Posts
    22

    Smile

    thanks for your advice

  4. #4
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    have you considered alternate index methods, such as CONTEXT?

    I have to question whether a 4K index field is useful.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    The maximum number of bytes allowed in a column for an index to be created is 3209 in 8i.

  6. #6
    Excellent thread!
    I have a question: do really this kind of indexes make some improvements? I never thought about something like this, because my business rules never lead to this kind of (specific) situation. But if I'll ever be in this situation ... hm, how big should a column be to make such a index usefull, and how many rows in the table. I think is a dificult situation, to need indexes on a 4XXX chars length column.
    Of course, if the dynamic is low, it is very usefull, but if the rows are often updated, I do not know.
    Of course, I could be very wrong, but this is why I try to find out...
    Any oppinions here?
    ovidius over!

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by tamilselvan
    The maximum number of bytes allowed in a column for an index to be created is 3209 in 8i.
    It is not that simple (besides, the size provided above is not correct anyhow).

    The maximum allowed index key length depends on your block size. So the mixumum allowed size reported in ORA-01450 varies, depending on which block size your index is using:

    ORA-01450 maximum key length (758) exceeded ->(2K Block)
    ORA-01450 maximum key length (1578) exceeded ->(4K block)
    ORA-01450 maximum key length (3218) exceeded ->(8K Block)
    ORA-01450 maximum key length (6498) exceeded ->(16K Block)

    The numbers in parenthesis represent the maximum index key length for that specific block size. Actually, even those numbers are not the theoretical upper limit, because they are calculated based on the default values of PCTFREE and INITRANS. So in theory you could "squeeze" a couple of additional bytes for max key length by reducing the values of PCTFREE and INITRANS below their defaults.

    If anybody is interested in how the maximum index key length is computed, there is a document (Doc.Id 136158.1) on Metalink that explains this in details.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width