oracle varchar 32k,[转]【Oracle Database 12c新特性】32k varchar2 max_string_size

在Oracle Database 12c中，我们可以为varchar2、nvarchar2和RAW数据类型指定32767 bytes 的最大长度了， 以便用户将更长的字符串存储在数据库中。

在12c之前的版本中，varchar2和nvarchar2数据类型的最大长度是4000 bytes，而raw是2000 bytes。

varcha2、nvarchar2和raw字段的定义长度将影响字段的内部存储方式

定义为4000字节或更小的varchar2、nvarchar2以及2000字节或更小的raw字段，将被inline存放

定义为4000字节以上的varchar2、nvarchar2以及2000字节以上的raw字段的话，被称作extended character data type columns，以out of line方式存储。

参数MAX_STRING_SIZE控制扩展数据类型extended data type的最大长度：

STANDARD 代表12c之前的长度限制，即varchar2、nvarchar2 4000 bytes， raw 是2000  bytes

EXTENDED 代表12c 32k strings新特性，varchar2、nvarchar2、raw最大长度32k  bytes

Extended character data types 扩展字符类型存在以下的限制：

不支持cluster table 簇表和index-organized tables索引组织表

不支持intrapartition的并行DDL、UPDATE和DELETE DML

不支持在Automatic Segment Space Management (ASSM)表空间上的intrapartition parallel direct-path inserts

为数据库配置扩展数据类型 的步骤如下：

关闭数据库实例  shutodnw immediate，如果是RAC则需要关闭所有实例

以upgrade模式启动数据库 实例 startup upgrade;

修改参数MAX_STRING_SIZE 为EXTENDED   ALTER SYSTEM SET MAX_STRING_SIZE = EXTENDED;

运行 @?/rdbms/admin/utl32k

重启数据库实例

@?/rdbms/admin/utl32k

...Database user "SYS", database schema "APEX_040200", user# "98" 13:29:59

...Compiled 0 out of 2998 objects considered, 0 failed compilation 13:30:00

...263 packages

...255 package bodies

...453 tables

...11 functions

...16 procedures

...3 sequences

...458 triggers

...1322 indexes

...207 views

...0 libraries

...6 types

...0 type bodies

...0 operators

...0 index types

...Begin key object existence check 13:30:00

...Completed key object existence check 13:30:00

...Setting DBMS Registry 13:30:00

...Setting DBMS Registry Complete 13:30:00

...Exiting validate 13:30:00

必须要将MAX_STRING_SIZE 设置为EXTENDED 否则无法使用extended character data type columns 。

之后我们可以创建具体有extended character data type columns 的表了。

当然我们也可以将已有的VARCHAR2, NVARCHAR2, 和RAW字段修改其长度， 具体使用ALTER TABLE MODIFY (COLUMN 命令。在此场景中Oracle将实施块中的长度扩展，而不将inline的存储迁移为以外部LOB存储。

实际上Oracle并不建议你广泛积极地将现有的varchar2的长度增加为4000 bytes以上，基于以下的原因：

很容易造成链式行row chaining

inline存储的数据行将被读取，不管该字段是否被select 。 实际inline的扩展字符类型显然会一定程度上影响性能。

为了迁移到新的out-of-line的存储扩展字符类型方式，用户需要重建表。否则任何类型的表重新组织方式例如alter table move都将无法打破inline存储

用户可以将32k的字段加入到现有的堆表，具体使用ALTER TABLE ADD 的DDL语句。

Data Pump导入导出以及SQL*Loader均支持extended character data type columns。

现有字段上的索引无法实现数据类型扩展，所以必须先将字段上的索引drop掉，再修改为扩展长度，之后再重建索引。

关于32k varchar2 max_string_size 、extended character data type columns的一些演示：

SQL> set linesize 200 pagesize 20000

SQL> select banner from v$version where rownum=1;

BANNER

-----------------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> show parameter MAX_STRING_SIZE

NAME TYPE VALUE

------------------------------------ ---------------------- -----------------------------

max_string_size string EXTENDED

SQL> CREATE TABLE long_varchar(id NUMBER,vc VARCHAR2(32767));

表已创建。

SQL> DESC long_varchar

名称 是否为空? 类型

----------------------------------------- -------- ----------------------------

ID NUMBER

VC VARCHAR2(32767)

SQL> insert into long_varchar values(1,rpad('MACLEAN',30000,'A'));

已创建 1 行。

SQL> commit;

提交完成。

SQL> alter system flush buffer_cache;

系统已更改。

SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from long_varchar;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)

------------------------------------ ------------------------------------

97217 1

SQL> alter system dump datafile 1 block 97217;

系统已更改。

SQL> oradebug setmypid

已处理的语句

SQL> oradebug tracefile_name

C:APPXIANGBLIdiagdbmsmacleanmacleanracemaclean_ora_5688.trc

tab 0, row 0, @0x1f65

tl: 59 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 02

col 1: [52]

00 54 00 01 01 0c 00 00 00 01 00 00 00 01 00 00 00 1f 50 05 00 20 05 00 00

00 00 03 15 e4 00 00 00 00 00 02 00 41 c5 73 00 41 c5 74 00 41 c5 75 00 41

c5 76

LOB

Locator:

Length: 84(52)

Version: 1

Byte Length: 1

LobID: 00.00.00.01.00.00.00.1f.50.05

Flags[ 0x01 0x0c 0x00 0x00 ]:

Type: BLOB

Storage: BasicFile

Enable Storage in Row

Characterset Format: IMPLICIT

Partitioned Table: No

Options: ReadWrite

Inode:

Size: 32

Flag: 0x05 [ Valid InodeInRow(ESIR) ]

Future: 0x00 (should be '0x00')

Blocks: 3

Bytes: 5604

Version: 00000.0000000002

DBA Array[4]:

0x0041c573 0x0041c574 0x0041c575 0x0041c576

end_of_block_dump

可以看到原生的32k varchar实际以BasicFile BLOB的方式out-of-line存储

SQL> create table convert_long(t1 int,t2 varchar2(20));

表已创建。

SQL> insert into convert_long values(1,'MACLEAN');

已创建 1 行。

SQL> commit;

提交完成。

SQL> create index ind_cl on convert_long(t2);

索引已创建。

SQL> alter table convert_long modify t2 varchar2(32767);

alter table convert_long modify t2 varchar2(32767)

*

第 1 行出现错误:

ORA-01404: ALTER COLUMN 将使索引过大

SQL> drop index ind_cl;

索引已删除。

SQL> alter table convert_long modify t2 varchar2(32767);

表已更改。

SQL> update convert_long set t2=rpad('MACLEAN',30000,'A');

已更新 1 行。

SQL> commit;

提交完成。

SQL> alter system flush buffer_cache;

系统已更改。

SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from convert_long;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)

------------------------------------ ------------------------------------

117121 1

SQL> oradebug setmypid

已处理的语句

SQL> oradebug tracefile_name

C:APPXIANGBLIdiagdbmsmacleanmacleanracemaclean_ora_4340.trc

可以看到形成了链式行 chained row

tab 0, row 0, @0x7aa

tl: 6120 fb: --H-F--N lb: 0x2 cc: 2

nrid: 0x0041c984.0

col 0: [ 2] c1 02

col 1: [6105]

4d 41 43 4c 45 41 4e 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41

41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41

41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41

41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41

41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41

41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41

41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41

41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41