Cách thức Oracle mở rộng kiểu dữ liệu VARCHAR2 trong 12c

Trong Oracle 12c, giới hạn của VARCHAR2 đã được nâng lên từ 4000 ký tự sang thành 32767 ký tự. Nhưng bất kể giới hạn này là bao nhiêu thì 1 block của Oracle vẫn giữ nguyên. Bởi vậy VARCHAR2 được mở rộng bằng một cột CLOB ẩn, bạn có thể xem ví dụ dưới đây:

SQL> create table t ( x int, y varchar2(32767));

Table created.

SQL>
SQL> insert into t values (1,rpad('x',32000,'x'));

1 row created.

SQL> select * from user_lobs
  2  where table_name = 'T'
  3  @pr
==============================
TABLE_NAME                    : T
COLUMN_NAME                   : Y
SEGMENT_NAME                  : SYS_LOB0000132155C00002$
TABLESPACE_NAME               : USERS
INDEX_NAME                    : SYS_IL0000132155C00002$
CHUNK                         : 8192
PCTVERSION                    :
RETENTION                     :
FREEPOOLS                     :
CACHE                         : YES
LOGGING                       : YES
ENCRYPT                       : NO
COMPRESSION                   : NO
DEDUPLICATION                 : NO
IN_ROW                        : YES
FORMAT                        : ENDIAN NEUTRAL
PARTITIONED                   : NO
SECUREFILE                    : YES
SEGMENT_CREATED               : YES
RETENTION_TYPE                : DEFAULT
RETENTION_VALUE               :

PL/SQL procedure successfully completed.

Dữ liệu bạn insert vào không phải là vấn đề, đó là giới hạn của VARCHAR2 quy định là chúng ta phải dùng CLOB thay thế. Ví dụ sau, chúng ta sẽ tạo lại bảng và chỉ insert một ký tự vào cột VARCHAR2, và xem cột CLOB ẩn vẫn được tạo ra

SQL> drop table t purge;

Table dropped.

SQL> create table t ( x varchar2(20000));

Table created.

SQL> insert into t values ('x');

1 row created.

SQL> select * from user_lobs
  2  where table_name = 'T'
  3  @pr
==============================
TABLE_NAME                    : T
COLUMN_NAME                   : X
SEGMENT_NAME                  : SYS_LOB0000132171C00001$
TABLESPACE_NAME               : USERS
INDEX_NAME                    : SYS_IL0000132171C00001$
CHUNK                         : 8192
PCTVERSION                    :
RETENTION                     :
FREEPOOLS                     :
CACHE                         : YES
LOGGING                       : YES
ENCRYPT                       : NO
COMPRESSION                   : NO
DEDUPLICATION                 : NO
IN_ROW                        : YES
FORMAT                        : ENDIAN NEUTRAL
PARTITIONED                   : NO
SECUREFILE                    : YES
SEGMENT_CREATED               : YES
RETENTION_TYPE                : DEFAULT
RETENTION_VALUE               :

PL/SQL procedure successfully completed.

Như chúng ta có thể thấy, đối với các cột VARCHAR2 có độ dài lớn hơn 4000 ký tự, Oracle đã ẩn chúng bằng một cột CLOB, vậy nếu chúng ta giới hạn số ký tự xuống nhỏ hơn 4000 ký tự thì sao?

SQL> truncate table t;

Table truncated.

SQL> alter table t modify y varchar2(1000);

Table altered.

SQL> select * from user_lobs
  2  where table_name = 'T'
  3  @pr
==============================
TABLE_NAME                    : T
COLUMN_NAME                   : Y
SEGMENT_NAME                  : SYS_LOB0000132155C00002$
TABLESPACE_NAME               : USERS
INDEX_NAME                    : SYS_IL0000132155C00002$
CHUNK                         : 8192
PCTVERSION                    :
RETENTION                     :
FREEPOOLS                     :
CACHE                         : YES
LOGGING                       : YES
ENCRYPT                       : NO
COMPRESSION                   : NO
DEDUPLICATION                 : NO
IN_ROW                        : YES
FORMAT                        : ENDIAN NEUTRAL
PARTITIONED                   : NO
SECUREFILE                    : YES
SEGMENT_CREATED               : YES
RETENTION_TYPE                : DEFAULT
RETENTION_VALUE               :

PL/SQL procedure successfully completed.

Chúng ta thấy ở trên, cột CLOB ẩn không bị mất đi khi chúng ta thay đổi độ dài về < 4000 ký tự, để làm cột này mất hẳn đi chúng ta cần drop cột VARCHAR2 này đi và tạo lại như sau:

SQL>
SQL> alter table t drop column y;

Table altered.

SQL> alter table t add y varchar2(1000);

Table altered.

SQL>
SQL> select * from user_lobs
  2  where table_name = 'T';

no rows selected