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

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


A copy of this was sent to badstreetboy_at_my-dejanews.com (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!!
>
>BSB
>
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- 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