Re: varchar2 DATA_LENGTH vs. NLS_CHARACTERSET vs. Oracle version

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Thu, 24 Dec 2020 16:50:57 +0300
Message-ID: <CAOVevU68knrGO5nJ68RFpv3KenBhpcpWcB4LyExBan7dS+JPzQ_at_mail.gmail.com>



Hi Manfred,

Even with default max_string_size=standard, you will get the same error in case of the index on 2 separate columns 1000char and no error in case of one column 2000char:

SQL> create table tab2(a varchar2(1000 char), b varchar2(1000 char), c varchar2(2000 char));

Table created.

SQL> create index ix1 on tab2(a,b);
create index ix1 on tab2(a,b)

                    *

ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

SQL> create index ix1 on tab2(c);

Index created.

On Thu, Dec 24, 2020 at 4:37 PM Thorsten Bruhns <dmarc-noreply_at_freelists.org> wrote:

> Hi,
> did you change the parameter max_string_size in 19c?
> I have the same error, when max_string_size=exetended with the
> following testcase:
>
> create table tab2(v varchar2(2000 char));
> create index i2 on tab2(v);
>
> I use the same characterset. The error is not raised when
> max_string_size is set to standard.
>
> Kind Regards
> Thorsten Bruhns
>
> Am Mi., 16. Dez. 2020 um 15:58 Uhr schrieb Pausch Manfred
> <Manfred.Pausch_at_tgw-group.com>:
> >
> > 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 24 2020 - 14:50:57 CET

Original text of this message