Re: Magic of varchar2

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.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)