Index key length formula for avoiding ORA-01450

From: Buboi Adrian <adryanbub_at_yahoo.com>
Date: Tue, 18 Sep 2012 02:27:31 -0700 (PDT)
Message-ID: <1347960451.9579.YahooMailNeo_at_web162602.mail.bf1.yahoo.com>

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_at_db> begin execute immediate 'drop table t_vb purge'; exception when others then null; end;       2  /
PL/SQL procedure successfully completed
user_at_db> create table t_vb (v1 varchar2(4000), v2 varchar2(4000), v3 varchar2(4000), v4 varchar2(943));  Table created
user_at_db> create index t_vb_idx on t_vb(v1,v2,v3,v4);  Index created
user_at_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_at_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_at_db> begin execute immediate 'drop table t_vc purge'; exception when others then null; end;       2  /
 PL/SQL procedure successfully completed user_at_db> create table t_vc (v1 varchar2(2000 char), v2 varchar2(2000 char), v3 varchar2(2000 char), v4 varchar2(943));  Table created
user_at_db> create index t_vc_idx on t_vc(v1,v2,v3,v4);  Index created
user_at_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_at_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_at_db> begin execute immediate 'drop table t_vcf purge'; exception when others then null; end;       2  /
 PL/SQL procedure successfully completed user_at_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_at_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_at_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_at_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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 18 2012 - 04:27:31 CDT