Re: Magic of varchar2

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Wed, 15 Mar 2006 18:58:51 +0100
Message-ID: <441856c6$0$7750$9b4e6d93@newsread4.arcor-online.net>

DA Morgan schrieb:
> Laurenz Albe wrote:
>

>> ... and secondly the total size of the index will depend on the length 
>> of the
>> indexed columns.
>>
>> Yours,
>> Laurenz Albe

>
>
> Tried to check it as follows:
>
> create table t1 (
> col1 VARCHAR2(10));
>
> create table t2 (
> col1 VARCHAR2(4000));
>
> BEGIN
> FOR i IN 1..50000 LOOP
> INSERT INTO t1 VALUES ('ABCDEFGHIJ');
> END LOOP;
> END;
> /
>
> BEGIN
> FOR i IN 1..50000 LOOP
> INSERT INTO t2 VALUES ('ABCDEFGHIJ');
> END LOOP;
> END;
> /
>
> CREATE INDEX ix_t1
> ON t1(col1);
>
> CREATE INDEX ix_t2
> ON t2(col1);
>
> exec dbms_stats.gather_schema_stats('UWCLASS', CASCADE=>TRUE);
>
> col segment_name format a30
>
> SELECT segment_name, sum(bytes), sum(blocks)
> FROM user_segments
> WHERE segment_type = 'INDEX'
> GROUP BY segment_name;
>
> SEGMENT_NAME SUM(BYTES) SUM(BLOCKS)
> ------------------------------ ---------- -----------
> IX_T2 1376256 168
> IX_T1 1376256 168
>
> I see no evidence of this being the case.

What Laurenz meant is probably:

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- 
------------------------------
db_block_size                        integer     8192

SQL> create table x(

   2  a varchar2(2000),
   3  b varchar2(2000),
   4  c varchar2(2000),
   5  d varchar2(2000),
   6  e varchar2(2000));

Table created.

SQL>
SQL> create table y(

   2  a varchar2(10),
   3  b varchar2(10),
   4  c varchar2(10),
   5  d varchar2(10),
   6  e varchar2(10));

Table created.

SQL>
SQL> create index y_idx on y(a,b,c,d);

Index created.

SQL>
SQL> create index x_idx on x(a,b,c,d);
create index x_idx on x(a,b,c,d)

                       *

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

In addition, to mentioned in this thread (all valid arguments), iirc Tom Kyte mentioned it , that on the client side by fetching rows memory will be allocated according defined column sizes, so inappropriate sizing will lead to memory vastage.

Best regards

Maxim Received on Wed Mar 15 2006 - 11:58:51 CST