Index key length formula for avoiding ORA-01450
- From: Buboi Adrian <adryanbub@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 18 Sep 2012 02:27:31 -0700 (PDT)
Hi list,
I'm trying to figure out the exact formula Oracle uses when computing the index
key length (for non-FBI and FBI with NLSSORT), and I think I'm stuck pretty
close, so any help would be appreciated.
I know the CREATE INDEX statement could be easily&fast tested for ORA-01450
(and good for future maintenance, etc), but, nonetheless, it would be great to
know the formula.
Firstly, some background information: Oracle 11.2.0.3, db_block_sizek,
character set=AL32UTF8, nls_length_semantics=BYTE.
The maximum index key length is around 80% of the block size (12958 for 16k).
This is the formula I got from a MOS note (sorry, I don't currently have access
to put the ID):
sum of the width of all indexed columns + the number of indexed columns +
length of the key (2 Bytes)Â + ROWID (6 Bytes) + the length of the rowid (1
byte)
The indexed columns length to be summed is as follows:
1. length
of normal index on column is MIN(LENGTH (column), 4000), where LENGTH (column)
is the
declared length in bytes of the character column, and can be taken from
user_tab_columns.data_length (or is the declared length in characters -Â Â Â
user_tab_columns.char_length when char_used=â??Câ?? - multiplied by 4 in
AL32UTF8)
2. length
of index on NLSSORT(column, 'NLS_SORT=BINARY_CI') is MIN(LENGTH (column) *
8 + 10, 2000),
where LENGTH(column) is as above
If we take the ROWID length as 8 bytes, the tests below show that for non-FBI
the formula seems to be correct, but for FBI i get a 3 bytes difference which I
am not able to explain.
I must mention I tried different variations of the above formula, trying to
take into account the column lengths (1 byte for <128, 3 bytes for >128), lock
bytes, but the below approach was the closest I got (and I take for granted the
limit from the ORA-01450 error message - 12958 - is indeed the real limit)
<CODE>
user@db> begin execute immediate 'drop table t_vb purge'; exception when others
then null; end;
     2 /
PL/SQL procedure successfully completed
user@db> create table t_vb (v1 varchar2(4000), v2 varchar2(4000), v3
varchar2(4000), v4 varchar2(943));
 Table created
user@db> create index t_vb_idx on t_vb(v1,v2,v3,v4);
 Index created
user@db> select sum(column_length)+count(*)+(2+8+1) as index_key_length from
user_ind_columns where index_name='T_VB_IDX';
 INDEX_KEY_LENGTH
----------------
          12958
user@db> alter table t_vb modify v4 varchar2(944);
 alter table t_vb modify v4 varchar2(944)
 ORA-01404: ALTER COLUMN will make an index too large
user@db> begin execute immediate 'drop table t_vc purge'; exception when others
then null; end;
     2 /
 PL/SQL procedure successfully completed
user@db> create table t_vc (v1 varchar2(2000 char), v2 varchar2(2000 char), v3
varchar2(2000 char), v4 varchar2(943));
 Table created
user@db> create index t_vc_idx on t_vc(v1,v2,v3,v4);
 Index created
user@db> select sum(column_length)+count(*)+(2+8+1) as index_key_length from
user_ind_columns where index_name='T_VC_IDX';
 INDEX_KEY_LENGTH
----------------
          12958
user@db> alter table t_vc modify v4 varchar2(944);
 alter table t_vc modify v4 varchar2(944)
 ORA-01404: ALTER COLUMN will make an index too large
user@db> begin execute immediate 'drop table t_vcf purge'; exception when
others then null; end;
     2 /
 PL/SQL procedure successfully completed
user@db> create table t_vcf (v1 varchar2(2000 char), v2 varchar2(2000 char), v3
varchar2(2000 char), v4 varchar2(2000 char), v5 varchar2(2000 char), v6
varchar2(2000 char), v7 varchar2(937));
 Table created
user@db> create unique index t_vcf_idx on
t_vcf(nlssort(v1,'NLS_SORT=BINARY_CI'),nlssort(v2,'NLS_SORT=BINARY_CI'),nlssort(v3,'NLS_SORT=BINARY_CI'),nlssort(v4,'NLS_SORT=BINARY_CI'),nlssort(v5,'NLS_SORT=BINARY_CI'),nlssort(v6,'NLS_SORT=BINARY_CI'),v7);
 Index created
user@db> select sum(column_length)+count(*)+(2+8+1) as index_key_length from
user_ind_columns where index_name='T_VCF_IDX';
 INDEX_KEY_LENGTH
----------------
          12955
user@db> alter table t_vcf modify v7 varchar2(938);
 alter table t_vcf modify v7 varchar2(938)
 ORA-01404: ALTER COLUMN will make an index too large
</CODE>
Thanks,
Adrian Buboi
--
//www.freelists.org/webpage/oracle-l
Other related posts: