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] On Behalf Of Kudryk, Mark (ES)
Sent: Monday, August 29, 2011 1:54 PM
To: 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] On Behalf Of Jonathan Lewis
Sent: Monday, August 29, 2011 12:28 PM
To: 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


----- Original Message ----- 
From: "Kudryk, Mark" <Mark_Kudryk_at_ADP.com>
To: <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