RE: ORA-01450: maximum key length (3215) exceeded -- Can't create index online with an nvarchar2 column
Date: Mon, 29 Aug 2011 15:54:11 -0400
Message-ID: <8702332A6FE19B4FB526047905C1A1EEA914CE79B2_at_DC2EXCVS01.ES.AD.ADP.COM>
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 Received on Mon Aug 29 2011 - 14:54:11 CDT