| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Index Key size for varchar2 columns.
A copy of this was sent to David Pattinson <dpattinson_at_enternet.com.au>
(if that email address didn't require changing)
On Wed, 24 Nov 1999 10:27:06 +1100, you wrote:
>Thanks Thomas,
>
>I have one more small question. If the key columns are varchar2, will the index
>keys have size of the maximum possible string that could be stored? In other words,
>if I have four VARCHAR2(24) columns in my PK will the index keys be 96 bytes or
>will they vary in length depending on the actual strings stored.
>
it is not an exact proof but it shows the point:
tkyte_at_8.0> create table t ( x varchar2(2000) );
Table created.
tkyte_at_8.0>
tkyte_at_8.0> insert into t select ascii( mod(rownum, 255) ) from all_objects;
17293 rows created.
tkyte_at_8.0>
tkyte_at_8.0> create index t_idx on t(x) storage ( initial 1k next 1k pctincrease 0
);
Index created.
tkyte_at_8.0>
tkyte_at_8.0> select bytes from user_segments where segment_name = 'T_IDX';
BYTES
294912
tkyte_at_8.0> select 17293*2000 from dual;
17293*2000
which shows its about 17 bytes per entry *MAX* -- no where near 2000. only what is used is indexed (CHAR on the other hand is always maxed out).
>Thanks again, David.
>
>Thomas Kyte wrote:
>
>> A copy of this was sent to David Pattinson <dpattinson_at_enternet.com.au>
>> (if that email address didn't require changing)
>> On Tue, 23 Nov 1999 10:43:05 +1100, you wrote:
>>
>> >How will Oracle build the index keys for a large compound PK? Does it
>> >only use the first n characters, or does it use the entire PK?
>> >
>> >Thanks, David.
>>
>> It uses the entire primary key (else uniqueness would be hard to confirm)...
>>
>> In Oracle8i, release 8.1 and up, you can employ index key compression where by
>> the leading edge of an index that repeats alot can be factored out to the block
>> level, resulting in much smaller indexes.
>>
>> --
>> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
>> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>>
>> Thomas Kyte tkyte_at_us.oracle.com
>> Oracle Service Industries Reston, VA USA
>>
>> Opinions are mine and do not necessarily reflect those of Oracle Corporation
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Nov 23 1999 - 18:12:08 CST
 |
 |