[20131116]12c的EXTENDED VARCHAR2与_scalar_type_lob_storage_threshold之2.txt
参考链接：http://space.itpub.net/267265/viewspace-776807/
google查询了一些资料：
发现：
SYS@test01p> @hide _scalar_type_lob_storage_threshold;
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ----------------------
_scalar_type_lob_storage_threshold threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB TRUE 4000 4000
--很明显这个参数可以控制字符串当作blob存储的长度，也就是缺省是4000.
--如果修改这个参数就可以控制存储长度是否选择blob方式保存的切换点。
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SYS@test01p> alter system set "_scalar_type_lob_storage_threshold"=10000;
System altered.
SCOTT@test01p> show parameter _scalar_type_lob_storage_threshold
NAME TYPE VALUE
------------------------------------ ---------------------------------------- ---------
_scalar_type_lob_storage_threshold integer 10000
--这样长度小于等于10000的字符不会使用blob保存。
drop table t1 purge ;
SCOTT@test01p> select * from user_lobs;
no rows selected
--当前用户没有lob字段类型。
create table t1 ( id number,a varchar2(10000),b varchar2(12000));
SCOTT@test01p> select table_name,segment_name from user_lobs;
TABLE_NAME SEGMENT_NAME
---------- ------------------------------
T1 SYS_LOB0000093548C00003$$
--仅仅在第3字段使用lob。
SCOTT@test01p> create index i_t1_a on t1(a);
create index i_t1_a on t1(a)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SCOTT@test01p> create index i_t1_b on t1(b);
create index i_t1_b on t1(b)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
--10000个字符已经超出了8K的数据块，在这些字段建立索引自然不行。当然在这么长字段建立索引意义不大。
--换一个角度思考，如果选择的数据块不是8k，而是32K应该就没有这个问题。
SCOTT@test01p> alter system set db_32k_cache_size=50M;
alter system set db_32k_cache_size=50M
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
--这个参数不允许在pdb上设置。
SYS@test> alter system set db_32k_cache_size=50M;
alter system set db_32k_cache_size=50M
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00382: 32768 not a valid block size, valid range [2048..16384]
--windows下依旧不支持32k的数据块。
SYS@test> alter system set db_16k_cache_size=50M;
System altered.
CREATE TABLESPACE t16k DATAFILE
'D:\APP\ORACLE\ORADATA\TEST\TEST01P\T16K01.DBF' SIZE 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
alter user scott quota unlimited on t16k;
SCOTT@test01p> alter table t1 move tablespace t16k;
Table altered.
SCOTT@test01p> create index i_t1_b on t1(b);
create index i_t1_b on t1(b)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
--不行！这样建立的索引默认users表空间。
SCOTT@test01p> create index i_t1_a on t1(a) tablespace t16k ;
Index created.
SCOTT@test01p> create index i_t1_b on t1(b) tablespace t16k ;
Index created.
--OK，通过。
总结：
1.建议这样的索引没有意义，只不过验证自己的想法是否正确。
2.也就是讲这些超长索引不是不能建，只要数据块变大，还是可以建立的，即使是在10g的环境下，当然我没有测试^_^。
--补充测试1:
SCOTT@test01p> alter system set "_scalar_type_lob_storage_threshold"=4000;
System altered.
SCOTT@test01p> create table t1 ( id number,a varchar2(10000),b varchar2(12000));
Table created.
SCOTT@test01p> select table_name,segment_name from user_lobs;
TABLE_NAME SEGMENT_NAME
---------- -------------------------
T1 SYS_LOB0000093573C00002$$
T1 SYS_LOB0000093573C00003$$
SCOTT@test01p> create index i_t1_a on t1(a) ;
create index i_t1_a on t1(a)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SCOTT@test01p> create index i_t1_a on t1(a) tablespace t16k ;
Index created.
--补充测试2:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> alter system set db_32k_cache_size=16M ;
System altered.
CREATE TABLESPACE t32k DATAFILE
'/u01/app/oracle11g/oradata/test/t32k01.dbf' SIZE 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 32K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SCOTT@test> select * from user_lobs;
no rows selected
create table t1 ( id number,a varchar2(4000),b varchar2(4000));
SCOTT@test> create index i_t1_a_b on t1(a,b);
create index i_t1_a_b on t1(a,b)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
--错误两个字段加起来达到8000字节，超出了6398限制。
SCOTT@test> create index i_t1_a_b on t1(a,b) tablespace t32k;
Index created.
--建立在32K的表空间OK通过!
参考链接：http://space.itpub.net/267265/viewspace-776807/
google查询了一些资料：
发现：
SYS@test01p> @hide _scalar_type_lob_storage_threshold;
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ----------------------
_scalar_type_lob_storage_threshold threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB TRUE 4000 4000
--很明显这个参数可以控制字符串当作blob存储的长度，也就是缺省是4000.
--如果修改这个参数就可以控制存储长度是否选择blob方式保存的切换点。
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SYS@test01p> alter system set "_scalar_type_lob_storage_threshold"=10000;
System altered.
SCOTT@test01p> show parameter _scalar_type_lob_storage_threshold
NAME TYPE VALUE
------------------------------------ ---------------------------------------- ---------
_scalar_type_lob_storage_threshold integer 10000
--这样长度小于等于10000的字符不会使用blob保存。
drop table t1 purge ;
SCOTT@test01p> select * from user_lobs;
no rows selected
--当前用户没有lob字段类型。
create table t1 ( id number,a varchar2(10000),b varchar2(12000));
SCOTT@test01p> select table_name,segment_name from user_lobs;
TABLE_NAME SEGMENT_NAME
---------- ------------------------------
T1 SYS_LOB0000093548C00003$$
--仅仅在第3字段使用lob。
SCOTT@test01p> create index i_t1_a on t1(a);
create index i_t1_a on t1(a)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SCOTT@test01p> create index i_t1_b on t1(b);
create index i_t1_b on t1(b)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
--10000个字符已经超出了8K的数据块，在这些字段建立索引自然不行。当然在这么长字段建立索引意义不大。
--换一个角度思考，如果选择的数据块不是8k，而是32K应该就没有这个问题。
SCOTT@test01p> alter system set db_32k_cache_size=50M;
alter system set db_32k_cache_size=50M
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
--这个参数不允许在pdb上设置。
SYS@test> alter system set db_32k_cache_size=50M;
alter system set db_32k_cache_size=50M
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00382: 32768 not a valid block size, valid range [2048..16384]
--windows下依旧不支持32k的数据块。
SYS@test> alter system set db_16k_cache_size=50M;
System altered.
CREATE TABLESPACE t16k DATAFILE
'D:\APP\ORACLE\ORADATA\TEST\TEST01P\T16K01.DBF' SIZE 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
alter user scott quota unlimited on t16k;
SCOTT@test01p> alter table t1 move tablespace t16k;
Table altered.
SCOTT@test01p> create index i_t1_b on t1(b);
create index i_t1_b on t1(b)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
--不行！这样建立的索引默认users表空间。
SCOTT@test01p> create index i_t1_a on t1(a) tablespace t16k ;
Index created.
SCOTT@test01p> create index i_t1_b on t1(b) tablespace t16k ;
Index created.
--OK，通过。
总结：
1.建议这样的索引没有意义，只不过验证自己的想法是否正确。
2.也就是讲这些超长索引不是不能建，只要数据块变大，还是可以建立的，即使是在10g的环境下，当然我没有测试^_^。
--补充测试1:
SCOTT@test01p> alter system set "_scalar_type_lob_storage_threshold"=4000;
System altered.
SCOTT@test01p> create table t1 ( id number,a varchar2(10000),b varchar2(12000));
Table created.
SCOTT@test01p> select table_name,segment_name from user_lobs;
TABLE_NAME SEGMENT_NAME
---------- -------------------------
T1 SYS_LOB0000093573C00002$$
T1 SYS_LOB0000093573C00003$$
SCOTT@test01p> create index i_t1_a on t1(a) ;
create index i_t1_a on t1(a)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SCOTT@test01p> create index i_t1_a on t1(a) tablespace t16k ;
Index created.
--补充测试2:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> alter system set db_32k_cache_size=16M ;
System altered.
CREATE TABLESPACE t32k DATAFILE
'/u01/app/oracle11g/oradata/test/t32k01.dbf' SIZE 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 32K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SCOTT@test> select * from user_lobs;
no rows selected
create table t1 ( id number,a varchar2(4000),b varchar2(4000));
SCOTT@test> create index i_t1_a_b on t1(a,b);
create index i_t1_a_b on t1(a,b)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
--错误两个字段加起来达到8000字节，超出了6398限制。
SCOTT@test> create index i_t1_a_b on t1(a,b) tablespace t32k;
Index created.
--建立在32K的表空间OK通过!
