Re: Help: what is the max length for an index field?

From: Thomas Kyte <>
Date: 1998/11/19
Message-ID: <36616b03.107711460_at_192.86.155.100>#1/1

A copy of this was sent to (if that email address didn't require changing) On Thu, 19 Nov 1998 02:21:43 GMT, you wrote:

>Can somesome tell me what is the max length for an indexed field? Is there a
>shorter limit on the length of an index key? For example, can I make a table
>with a 4000 byte varchar2 field and then create an index on that field?

the size of an index entry is a function of the block size. For example, I have an 8k block and therefore

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

Table created.

SQL> create index foo_idx on foo(x);
create index foo_idx on foo(x)


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

The max key length is a little less then half the block size. (so a 2k block size would give you a smaller max key length)

Why would you index a varchar2(4000)?? isn't that a little large....

>Please help. Thanks!!
>-----------== Posted via Deja News, The Discussion Network ==----------
> Search, Read, Discuss, or Start Your Own

Thomas Kyte
Oracle Government
Herndon VA

--    -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.
Received on Thu Nov 19 1998 - 00:00:00 CET

Original text of this message