Re: indexing VARCHAR2 fields

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Sat, 21 Apr 2001 08:42:19 +0200
Message-ID: <3AE12BCB.6162E29C@0800-einwahl.de>

I don't know why. I assume it is needed for constructing a valid b*-tree.

You just can prove that it *is* the case (mind the overhead for the rowid and the varchar2 length fields!):

SQL> select value from v$parameter where name = 'db_block_size';

VALUE

---

16384

SQL>
SQL> drop table testtab;

Table dropped.

SQL> create table testtab (

  2          a varchar2 (4000)
  3          , b varchar2 (4000)
  4          , c varchar2 (2487)
  5          , d varchar2 (2488)

  6 );

Table created.

SQL>
SQL> create index testinda on testtab (a);

Index created.

SQL> create index testindb on testtab (a, b); create index testindb on testtab (a, b)

                         *

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

SQL> create index testindc on testtab (a, c);

Index created.

SQL> create index testindd on testtab (a, d); create index testindd on testtab (a, d)

                         *

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

Martin

Rene Nyffenegger wrote:
>
> >The recordsize must be <= db_block_size/3. So you *must* have a db with
> >db_block_size = 16384 at least.
> >
> >Martin
>
> Why's that?
>
> Rene
>
> --
> Rene Nyffenegger
> rene dot nyffenegger at adp-gmbh dot ch
>
>
Received on Sat Apr 21 2001 - 01:42:19 CDT