RE: ORA-01450: maximum key length (3215) exceeded -- Can't create index online with an nvarchar2 column
Date: Tue, 30 Aug 2011 13:51:59 -0400
Message-ID: <8702332A6FE19B4FB526047905C1A1EEA914D399BA_at_DC2EXCVS01.ES.AD.ADP.COM>
I was relying on the Oracle documentation that states an nvarchar when declared is defines size in terms of characters, not byte; as opposed to varchar2 which can be defined size wise in bytes or chars. But documentation can be wrong...
To confirm, I needed to output the definition of the IOT table to see how its NVARCHAR2 column is defined. When I define that column to be 351 characters, the error occurs when the IOT is created, so there's no IOT to examine. Therefore, I reduced the size of the nvarchar2 field from 351 to 350, for which I know I can create an online index. I filled the table with enough data such that the online index creation would take enough time for me to examine the IOT column definitions before the index creation completed and the IOT was dropped.
Step 1 -- Create the table.
create table mk_test_length_n350 (
invoice_id number not null,
data_field_id number not null,
additional_data_n350 nvarchar2(350) not null);
Step 2 - MK_Test_Length_N350 table column definitions
select table_name, column_name, data_type, data_length, character_set_name, char_col_decl_length, char_length, char_used
from all_tab_columns
where table_name = 'MK_TEST_LENGTH_N350';
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH CHAR_LENGTH CHAR_USED MK_TEST_LENGTH_N350 ADDITIONAL_DATA_N350 NVARCHAR2 1050 NCHAR_CS 1050 350 C
Step 3 - Create the online index and get the name of the IOT from the tracefile. Name is SYS_JOURNAL_426298.
create index MK_INDEX_NVARCHAR_N350 ON mk_test_length_n350 (data_field_id, UPPER(additional_data_n350), invoice_id) tablespace indx online
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 1 0 0 Execute 1 61.31 60.23 18602 17611 26530 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 61.32 60.24 18602 17612 26530 0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 121
create table "DOCPADMIN"."SYS_JOURNAL_426298" (C0 NUMBER, C1 NVARCHAR2(1050), C2 NUMBER, opcode char(1), partno number, rid rowid, primary key( C0, C1, C2 , rid )) organization index TABLESPACE "INDX"
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.02 0.02 0 0 1 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.03 0.02 0 0 1 0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 121 (recursive depth: 1)
Step 4 - SYS_JOURNAL_426298 (IOT) table column definitions
select table_name, column_name, data_type, data_length, character_set_name, char_col_decl_length, char_length, char_used
from all_tab_columns
where table_name like 'SYS_JOURNAL%';
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH CHAR_LENGTH CHAR_USED SYS_JOURNAL_426298 C1 NVARCHAR2 3150 NCHAR_CS 3150 1050 C
The additional_data_n350 column is originally defined as NVARCHAR2(350). As it's UTF8, its data length is 3 times that size: 1050. When the IOT SYS_JOURNAL_426298 is created, Oracle is using the data_length value to define its NVARCHAR2 field, but this definition is in characters (CHAR_USED is C), and thus the data length is 3150. For this table, it's key length is 3150 + 22 + 22 + 3 + 2 + 8 + 1 = 3210 < 3215. The addition of another character to the column (size would be 351) will increase the key length by 9 (3 * 3), 3219 > 3215.
It looks like Oracle is unnecessarily expanding the size of the column.
Mark
-----Original Message-----
From: D'Hooge Freek [mailto:Freek.DHooge_at_uptime.be]
Sent: Tuesday, August 30, 2011 12:01 AM
To: Kudryk, Mark (ES); oracle-l_at_freelists.org
Subject: RE: ORA-01450: maximum key length (3215) exceeded -- Can't create index online with an nvarchar2 column
Mark,
How did you conclude that the length of the C1 column in the iot was in characters and not in bytes? If the nls_length_semantics is set to byte and no CHAR keyword was used when defining the column, then it should be defined in bytes.
Unless the nls_length_semantics is altered in the session itself, but this should show in the sql trace.
Kind regards,
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kudryk, Mark Sent: maandag 29 augustus 2011 22:38
To: oracle-l_at_freelists.org
Subject: RE: ORA-01450: maximum key length (3215) exceeded -- Can't create index online with an nvarchar2 column
select *
from nls_database_parameters
where parameter IN ('NLS_LENGTH_SEMANTICS','NLS_NCHAR_CHARACTERSET')
PARAMETER
VALUE
NLS_NCHAR_CHARACTERSET
UTF8
NLS_LENGTH_SEMANTICS
BYTE
From: David Fitzjarrell [mailto:oratune_at_yahoo.com]
Sent: Monday, August 29, 2011 2:31 PM
To: Kudryk, Mark (ES); oracle-l_at_freelists.org
Subject: Re: ORA-01450: maximum key length (3215) exceeded -- Can't create index online with an nvarchar2 column
Before you do check how NLS_LENGTH_SEMANTICS is set; it may be set for CHAR rather than BYTE. If it's set correctly (BYTE) and this still occurs submit an SR.
David Fitzjarrell
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-lReceived on Tue Aug 30 2011 - 12:51:59 CDT