RE: ORA-01450: maximum key length (3215) exceeded -- Can't create index online with an nvarchar2 column
Date: Mon, 29 Aug 2011 16:37:37 -0400
Message-ID: <8702332A6FE19B4FB526047905C1A1EEA914CE7BB0_at_DC2EXCVS01.ES.AD.ADP.COM>
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
From: "Kudryk, Mark" <Mark_Kudryk_at_ADP.com>
To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
Sent: Monday, August 29, 2011 1:08 PM
Subject: RE: ORA-01450: maximum key length (3215) exceeded -- Can't create index online with an nvarchar2 column
I've looked at the trace. Here's the portion where the index is being created...
create index MK_INDEX_NVARCHAR_N351 ON mk_test_length_n351 (data_field_id, UPPER(additional_data_n351), invoice_id) tablespace indx online
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 1 0 0 Execute 1 0.02 0.01 0 1 27 0 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.03 0 2 27 0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 121
And here is the portion where the IOT is being created.
create table "DOCPADMIN"."SYS_JOURNAL_425558" (C0 NUMBER, C1 NVARCHAR2(1053), 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.02 0.00 0 0 0 0 Execute 1 0.01 0.01 0 0 3 0 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.01 0 0 3 0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 121 (recursive depth: 1)
Initially when I read this, I assumed the NVARCHAR2(1053) was defined in bytes (351 * 3). But it's not... it's in characters, the data length of this field in the IOT will actually be 3159. Plugging this value into the formula, I get an overall length of 3217, which exceeds the limit.
I'll raise an SR.
Mark
-----Original Message-----
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Kudryk, Mark (ES)
Sent: Monday, August 29, 2011 1:54 PM
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
Subject: RE: ORA-01450: maximum key length (3215) exceeded -- Can't create index online with an nvarchar2 column
Thanks. Mea Culpa on the length.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Jonathan Lewis
Sent: Monday, August 29, 2011 12:28 PM
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
Subject: Re: ORA-01450: maximum key length (3215) exceeded -- Can't create index online with an nvarchar2 column
I would enable sql_trace and look at the create statement produced for the
journalling IOT.
Your 2011 is wrong, it should be 1,111; if (for no good reason) you multiply the
column length by 3 (again), the answer comes to 3217.
So, taking a guess, Oracle has taken the data length as 3 x char_length, and then the journal code has defined it's char_length as the result - which has taken ITS data_length to 9 times the original char_length.
Raise an SR if I'm right.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com<http://jonathanlewis.wordpress.com/>
- Original Message ----- From: "Kudryk, Mark" <Mark_Kudryk_at_ADP.com<mailto:Mark_Kudryk_at_ADP.com>> To: <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Sent: Monday, August 29, 2011 5:05 PM Subject: ORA-01450: maximum key length (3215) exceeded -- Can't create index online with an nvarchar2 column
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.
--
http://www.freelists.org/webpage/oracle-l
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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Aug 29 2011 - 15:37:37 CDT