Re: varchar2 DATA_LENGTH vs. NLS_CHARACTERSET vs. Oracle version

Hi Manfred,

There is a good doc for that in MOS: AL32UTF8 / UTF8 (Unicode) Database Character Set Implications (Doc ID 788156.1) You need B.5) Do I need to convert from UTF8 to AL32UTF8 ?

*Oracle UTF8 is Unicode revision 3.0 in 8.1.7 and up.AL32UTF8 is updated with newer Unicode versions in each major release, in Oracle RDBMS 18.1 it is updated to Unicode 9.0. See also Note 260893.1 <https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=788156.1&id=260893.1> Unicode
character sets in the Oracle database. *

On Wed, Dec 16, 2020 at 5:58 PM Pausch Manfred <Manfred.Pausch_at_tgw-group.com> wrote:

> Hello all,
>
> have 2 databases. One is 11.2.0.4, the other is 19c.
> (yes, it's time for upgrading to 19c *g*)
>
>
> 11.2 has NLS_CHARACTERSET = UTF8
> 19c has NLS_CHARACTERSET = AL32UTF8
>
> On both DBs I have a table
> CREATE TABLE mytable
> ( "PRESETNUMBERCONFIGPRJID" NUMBER(12,0),
> "PRESETNUMBER" NUMBER(12,0),
> "SHELLSIZE" VARCHAR2(1000 CHAR),
> "FILMTYPE" VARCHAR2(1000 CHAR),
> <some other insignificant columns>
> )
>
>
>
> On 11.2 I have an index (because of a unique constraint):
> CREATE UNIQUE INDEX myindex ON mytable ("SHELLSIZE", "FILMTYPE") ;
>
> On 19c creation of the same index fails with:
> ORA-01450: maximum key length (6398) exceeded
>
> Tablespace blocksize on both databases is 8k.
>
>
> Querying USER_TAB_COLS with
> SELECT data_type, data_length, char_col_decl_length, char_length, char_used
> FROM user_tab_cols
> WHERE table_name = mytable
> AND column_name in ('SHELLSIZE', 'FILMTYPE');
>
> Returns
> On 11.2:
> DATA_TYPE DATA_LENGTH CHAR_COL_DECL_LENGTH CHAR_LENGTH C
> ---------- ----------- -------------------- ----------- -
> VARCHAR2 3000 3000 1000 C
> VARCHAR2 3000 3000 1000 C
>
>
> And on 19c:
> DATA_TYPE DATA_LENGTH CHAR_COL_DECL_LENGTH CHAR_LENGTH C
> ---------- ----------- -------------------- ----------- -
> VARCHAR2 4000 4000 1000 C
> VARCHAR2 4000 4000 1000 C
>
>
> As I understand index creation on 19c (NLS_CHARACTERSET = AL32UTF8) fails
> because
> data_length of both columns (4000+4000) is more than the maximum key
> length (6398).
>
> But why the data_length for a "varchar2(xxx CHAR)" column is different on
> both databases?
> Has it to do with the different NLS_CHARACTERSET settings?
> Or with the different database versions?
> Or something totally different?
>
> As far as I know UTF8 encodings are variable-width, which use up to 4bytes
> (depending on the character you want to store...)
>
> So maybe someone could shed some light, please :-)
>
>
> Thanks in advance
> Manfred
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l