Re: Index key length formula for avoiding ORA-01450

From: Buboi Adrian <adryanbub_at_yahoo.com>
Date: Tue, 18 Sep 2012 07:34:22 -0700 (PDT)
Message-ID: <1347978862.54478.YahooMailNeo_at_web162601.mail.bf1.yahoo.com>

Jonathan, thanks for your reply.
Using your valuable input I performed some tests which indicate that the formula used by Oracle for checking the index key length is:

sum of length of all indexed columns length byte(s) + sum of the length of all indexed columns + length of the ROWID length byte (1 byte) + length of the ROWID (6 bytes)

where
length of indexed column length byte(s) = CASE WHEN LENGTH(indexed_column) < 128 THEN 1 ELSE 2 END

The query below returns the key length of a potential index (on the desired columns):

<CODE>

select sum(utc.data_length) + sum(case when utc.data_length < 128 then 1 else 2 end) + (6 + 1) as index_key_length   from user_tab_columns utc
where utc.TABLE_NAME = '<TABLE_NAME>'
   and utc.COLUMN_NAME in ('<COLUMN_LIST>');
</CODE>

For each desired NLSSORT (column) from index, utc.data_length should be replaced with MIN(utc.data_length * 8 + 10, 2000)

The query below returns the key length of an existing index:

<CODE>

select sum(case when utc.data_length <128 then 1 else 2 end) + sum(uic.column_length)+ 1 + 6 as index_key_length

 from user_ind_columns uic
  join user_tab_columns utc
    on uic.TABLE_NAME=utc.TABLE_NAME
   and uic.COLUMN_NAME=utc.COLUMN_NAME

where index_name='<INDEX_NAME>'
</CODE>

Adrian

---

 From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> To: oracle-l_at_freelists.org
Sent: Tuesday, September 18, 2012 1:24 PM Subject: Re: Index key length formula for avoiding ORA-01450  

A couple of corrections:

The length of the full key is not stored, it's derived in the dump For columns longer than 127 bytes the column length is stored as 2 bytes, not three bytes.

The length reported in the ORA-01450 is only about the key length, and does not consider lock, flag and row directory bytes.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

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

Received on Tue Sep 18 2012 - 09:34:22 CDT