Re: ora-1450 even though max key length is well below the max

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Fri, 23 Jun 2006 15:09:47 GMT
Message-ID: <%uTmg.1056$Wh.468@trnddc04>

Matthew Winn wrote:
> On Thu, 22 Jun 2006 15:39:50 GMT, Chuck <skilover_nospam_at_bluebottle.com> wrote:

>>  Does anyone around here read? I'll say it one last time... this works in
>>  a 9205 instance with the exact same DDL. If as you say it's based on the
>>  max defined width of the varchars how does this work on 9205?

>
> It doesn't work for me:
>
> SQL> select * from v$version where rownum <= 2;
> Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
> PL/SQL Release 9.2.0.5.0 - Production
>
> SQL> create table test_tab (c1 varchar2(4000), c2 varchar2(4000));
> Table created.
>
> SQL> create index test_idx on test_tab(c1, c2);
> ORA-01450: maximum key length (6398) exceeded
>

My index is on a DATE and two VARCHAR2(2000) columns, db_block_size = 8192.

SQL> SELECT * FROM v$version

BANNER

---

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

5 rows selected.
SQL> SELECT VALUE FROM v$parameter WHERE NAME = 'db_block_size'

VALUE

---

8192

1 row selected.
SQL> CREATE TABLE mytable (logdate DATE, uri VARCHAR2(2000), referrer VARCHAR2(2000), pageviews INTEGER)
Table created.
SQL> CREATE INDEX myindex ON mytable (logdate, uri, referrer) Index created. Received on Fri Jun 23 2006 - 10:09:47 CDT