通过 WordPress.com 设计一个这样的站点
从这里开始

Increased Size Limit for VARCHAR2, NVARCHAR2, and RAW Data Types#JoelKallmanDay

I’ve finished reading the blog post published by Tim Hall on his blog on September 27, 2022. Many people and friends have reposted his post, which no doubt holds great emotional significance for everyone.Therefore, today I also want to write something to support him. But what to write?Since this is my first time, I will write a few new Oracle features.

On the New Features website, there is a new feature about extending data typesThe maximum size of the VARCHAR2, NVARCHAR2, and RAW data types has been increased from 4,000 to 32,767 bytes.

I thought it was very interesting and enjoyed it, so I would like to introduce it.

Take the varchar2 data type as an example:

The VARCHAR2 data type specifies a variable-length character string in the database character set. You specify the database character set when you create your database.

When you create a table with a VARCHAR2 column, you must specify the column length as size optionally followed by a length qualifier. The qualifier BYTE denotes byte length semantics while the qualifier CHAR denotes character length semantics. In the byte length semantics, size is the maximum number of bytes that can be stored in the column. In the character length semantics, size is the maximum number of code points in the database character set that can be stored in the column. A code point may have from 1 to 4 bytes depending on the database character set and the particular character encoded by the code point. Oracle recommends that you specify one of the length qualifiers to explicitly document the desired length semantics of the column. If you do not specify a qualifier, the value of the NLS_LENGTH_SEMANTICS parameter of the session creating the column defines the length semantics, unless the table belongs to the schema SYS, in which case the default semantics is BYTE.

Oracle stores a character value in a VARCHAR2 column exactly as you specify it, without any blank-padding, provided the value does not exceed the length of the column. If you try to insert a value that exceeds the specified length, then Oracle returns an error.

However, in the Oracle 19c official documentation description,The minimum value of size is 1. The maximum value is:

  • 32767 bytes if MAX_STRING_SIZE = EXTENDED
  • 4000 bytes if MAX_STRING_SIZE = STANDARD

While size may be expressed in bytes or characters (code points) the independent absolute maximum length of any character value that can be stored into a VARCHAR2 column is 32767 or 4000 bytes, depending on MAX_STRING_SIZE. For example, even if you define the column length to be 32767 characters, Oracle returns an error if you try to insert a 32767-character value in which one or more code points are wider than 1 byte. The value of size in characters is a length constraint, not guaranteed capacity. If you want a VARCHAR2 column to be always able to store size characters in any database character set, use a value of size that is less than or equal to 8191, if MAX_STRING_SIZE = EXTENDED, or 1000, if MAX_STRING_SIZE = STANDARD.

However, I found that the MAX_STRING_SIZE parameter does not need to be set to EXTENDED to create the varchar2 extension data type and insert the data successfully.The following is my test in each version of the database. Isn’t it interesting that there is a discrepancy with the official Oracle documentation?

DB Version 19.15


DB Version 19.15

db 19.15

DB Version 19.4

sqlplus / as sysdba
set timing on time on

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 10 13:43:15 2022
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

show parameter max_string_size

NAME TYPE VALUE


max_string_size string STANDARD

14:02:42 SYS@JiekeXu> create table T1(C clob, E varchar2(32737));

Table created.
14:03:09 SYS@JiekeXu> insert into t1 select lpad(rownum,32767,'X'),lpad(rownum,32767,'X') from dual connect by level <=10;

10 rows created.
14:03:14 SYS@JiekeXu> commit;

Commit complete.


14:03:19 SYS@JiekeXu> set autot trace stat
14:04:04 SYS@JiekeXu> select C from t1;

db version 19.3

db 19.3

DB Version 12.2

db 12.2

Because I don’t have the database versions of Oracle 12.1.0.1 and 12.0.1.2 for the moment, I can’t test it. I wonder whether these two versions need to set parameters and restart the database.Interested friends with the environment can test whether the following results are correct.

db 12.1

So what are the benefits of Extended Data types?

Increasing the allotted size for these data types allows users to store more information in character data types before switching to large objects (LOBs). This is especially useful for brief textual data types and the capabilities to build indexes on these types of columns And Extended VARCHAR2 reduces the number of network round-trips.

So what’s the problem? You know what? Give me a homework assignment. It’s late today.

Joel Kallman, we’ll miss you forever, the enormous “Thank you” to great and amazing contribution to oracle community.

update

Today, my friend sent me a screenshot of version 12.1.0.2.The result is a direct error in the SQL construct sentence.

db 12.1.0.2

In the test process of version 12.2, it is found that no matter how large the size is, it can be successfully created.So why is this happening? Let’s move on.

Now for the reveal 开始揭秘

create table T1(id int, E varchar2(32767));
CREATE SEQUENCE SEQ_T1 MINVALUE 1 MAXVALUE 99999999999999 INCREMENT BY 1 START WITH 1 CACHE 50 ORDER NOCYCLE;
insert into t1 values(SEQ_T1.nextval,lpad('x',32767,'x'));
insert into t1 values(SEQ_T1.nextval,lpad('x',32768,'x'));
select length(E) from t1;

And you can see that whether I use 32767 or 32768 I’m going to end up with a length of 4000.So the official document says there is no error, we have to modify the parameter MAX_STRING_SIZE = EXTENDED .

database must in UPGRADE mode
startup UPGRADE

Therefore, it can be seen from the above that under normal circumstances, the maximum value of varchar2 can only be 4000, and the excess part will be truncated. After the database is started in upgrade mode, utl32k.sql is run, and parameters are modified, vatchar2 can store up to 32767 bytes, and the excess bytes are truncated.

If your database is RAC, close node2 and change CLUSTER_DATABASE=FALSE parameter.

node2: shu immediate

node1:
alter system set CLUSTER_DATABASE=FALSE scope=spfile;
alter system set MAX_STRING_SIZE = EXTENDED scope=spfile;
shu immediate
startup upgrade
@?/rdbms/admin/utl32k.sql

alter system set CLUSTER_DATABASE=TRUE scope=spfile;
shu immediate
startup
show parameter max_string_size
create table T1(E varchar2(32767));
insert into t1 values(lpad(‘x’,32767,’x’));
commit;
select length(E) from t1;

node1: startup

广告

《“Increased Size Limit for VARCHAR2, NVARCHAR2, and RAW Data Types#JoelKallmanDay”》 有 4 条评论

  1. Interesting discovery.

    I tested this on 19.11 (in a PDB) – and on that version this behaviour appears ONLY if I run in the SYS schema. It gets you the worst possible combination – the column is created as a CLOB (so you get all the CLOB overheads) but the clob stores only the first 4,000, with the remainder of the LOB block being padded with “space” (i.e. 0x00 0x20).

    Fortunately the data can be stored as single byte (rather than the usual double-byte clob character set) but it does mean that if you have a strings longer than 3960 bytes it move to the LOB segment.

    Regards
    Jonathan Lewis

  2. As usual, later from 12.2 is more user-friendly and smart than 12.1 or 12.0 on extended VARCHAR2 datatype, thanks for your sharing!

发表评论

Fill in your details below or click an icon to log in:

WordPress.com 徽标

您正在使用您的 WordPress.com 账号评论。 注销 /  更改 )

Twitter picture

您正在使用您的 Twitter 账号评论。 注销 /  更改 )

Facebook photo

您正在使用您的 Facebook 账号评论。 注销 /  更改 )

Connecting to %s

%d 博主赞过: