ORA-01450: maximum key length (3215) exceeded -- Can't create index online with an nvarchar2 column
- From: "Kudryk, Mark" <Mark_Kudryk@xxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 29 Aug 2011 12:05:12 -0400
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.
Other related posts:
