RE: 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 16:37:37 -0400
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@xxxxxxxxx]
Sent: Monday, August 29, 2011 2:31 PM
To: Kudryk, Mark (ES); oracle-l@xxxxxxxxxxxxx
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@xxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On
Behalf Of Kudryk, Mark (ES)
Sent: Monday, August 29, 2011 1:54 PM
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On
Behalf Of Jonathan Lewis
Sent: Monday, August 29, 2011 12:28 PM
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxx<mailto:Mark_Kudryk@xxxxxxx>>
To: <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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.
--
//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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
