ORA-01450: maximum key length (3215) exceeded -- Can't create index online with an nvarchar2 column
Date: Mon, 29 Aug 2011 12:05:12 -0400
Message-ID: <8702332A6FE19B4FB526047905C1A1EEA914C4C380_at_DC2EXCVS01.ES.AD.ADP.COM>
We have a table for which one of the columns is going to have its type changed from varchar2(1000) to nvarchar2(1000). This column is part of an index which will be created using the ONLINE keyword. When I attempted to create the index using the new column type, I got the following error:
15:40:29 [CREATE - 0 row(s), 0.000 secs] [Error Code: 604, SQL State: 60000] ORA-00604: error occurred at recursive SQL level 1 ORA-01450: maximum key length (3215) exceeded
I began to dig into ORA-01450, and found this informative page http://www.pythian.com/news/1111/ora-01450-during-online-index-rebuild/ and also read the [Metalink article|https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=136158.1] it referred to. These articles indicated that when an ONLINE keyword is used, Oracle uses an IOT table to create the index. The IOT does have a key length limit which is a function of the database's block size.
Block Size
Max. Key Length
2KB
755
4KB
1575
8KB
3215
16KB
3800
Our block size is 8KB.
select bytes/blocks as "Block Size" from user_segments where rownum = 1;
Block Size
8192
The articles cited above provide a formula for calculating the key length of the IOT. I'm still unable to get my numbers to add up when using an nvarchar2 column.
I first created a test table using a varchar2, and I determined that I'm able to have a maximum varchar2 column size of 3155 and be able to create the index online, at 3156 characters I get the error above.
create table mk_test_length_vbase (
invoice_id number not null,
data_field_id number not null,
v_additional_data varchar2(3156) not null);
create index MK_INDEX_VARCHAR_BASE ON mk_test_length_vbase (data_field_id, UPPER(v_additional_data), invoice_id) tablespace indx online;
Using the formula in the webpage and metalink cited, my key length should be 3156 (v_additional_data) + 22 (invoice_id) + 22 (data_field_id) + 3 (number of columns) + 2 (length of key) + 8 (ROWID) + 1 (length of rowid) = 3214. (The metalink article was written in 2008, and I've read that in 10g the ROWID size has increased to 8 bytes). I can't account for the last byte, but the metalink article says one needs to account for the return type of the function (in my case UPPER). Regardless, the numbers pretty much add up.
When I switched to an nvarchar2, through experimentation I found I could only have a maximum size field of 350 to be able to create the index online. At 351 the online index creation failed.
create table mk_test_length_n351 (
invoice_id number not null,
data_field_id number not null,
additional_data_n351 nvarchar2(351) not null);
create index MK_INDEX_NVARCHAR_N351 ON mk_test_length_n351 (data_field_id, UPPER(additional_data_n351), invoice_id) tablespace indx online;
15:55:47 [CREATE - 0 row(s), 0.000 secs] [Error Code: 604, SQL State: 60000] ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded
Our character set is UTF8, which can mean a character can take up 1-3 bytes (see http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm#sthref741). The data length of the nvarchar2 field is 1053.
select table_name, column_name, data_type, data_length, char_length
from all_tab_columns
where table_name = 'MK_TEST_LENGTH_N351';
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_LENGTH
MK_TEST_LENGTH_N351 INVOICE_ID NUMBER 22 0 MK_TEST_LENGTH_N351 DATA_FIELD_ID NUMBER 22 0 MK_TEST_LENGTH_N351 ADDITIONAL_DATA_N351 NVARCHAR2 1053 351
Using the formula above, I calculate the key length to be 1053 (v_additional_data) + 22 (invoice_id) + 22 (data_field_id) + 3 (number of columns) + 2 (length of key) + 8 (ROWID) + 1 (length of rowid) = 2011, which is not close to 3215. As we are using UTF8, I would have expected that the number of nvarchar2 characters I could have used would have been 1/3 of a varchar2 column (3155 vs. 1031), and not approximately 1/9th (350) the size.
Can anyone shed any light on this?
Thank you in advance,
Mark
This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, notify the sender immediately by return email and delete the message and any attachments from your system.
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Aug 29 2011 - 11:05:12 CDT