[20191210]降序索引疑问3.txt

--//昨天写的http://blog.itpub.net/267265/viewspace-2667573/=>[20191209]降序索引疑问.txt
--//还有另外的疑问,就是降序索引字段类型的长度的问题.或者讲我以前没注意这个细节.
--//链接:http://blog.itpub.net/267265/viewspace-2667580/=>[20191209]降序索引疑问2.txt
--//我当时的结论是降序索引字段类型raw的长度是1.5*原来长度+1.
--//我可能忽略一个问题,可能raw类型有最大长度限制,很有可能是4000.
--//如果按照前面的公式反推,(4000-1)/3*2 = 2666长度.降序字段raw长度就是4000.补充测试看看.
--//通过例子说明:

1.环境:
SCOTT@book> @ ver1
PORT_STRING         VERSION        BANNER
------------------- -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试:
SCOTT@book> create table t (v1 varchar2(2666),v2 varchar2(2667),v3 varchar2(4000));
Table created.

create index if_t_v1 on t(v1 desc);
create index if_t_v2 on t(v2 desc);
create index if_t_v3 on t(v3 desc);

SCOTT@book> select column_name,data_type,data_length,data_precision,data_scale from DBA_TAB_COLUMNS where owner=user and table_name='T';
COLUMN_NAME          DATA_TYPE  DATA_LENGTH DATA_PRECISION DATA_SCALE
-------------------- ---------- ----------- -------------- ----------
V1                   VARCHAR2          2666
V2                   VARCHAR2          2667
V3                   VARCHAR2          4000

SCOTT@book> SELECT * FROM DBA_IND_COLUMNS WHERE TABLE_OWNER = USER AND TABLE_NAME='T';
INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME  COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
----------- ---------- ----------- ---------- ------------ --------------- ------------- ----------- ----
SCOTT       IF_T_V1    SCOTT       T          SYS_NC00004$               1          4000           0 DESC
SCOTT       IF_T_V2    SCOTT       T          SYS_NC00005$               1          4000           0 DESC
SCOTT       IF_T_V3    SCOTT       T          SYS_NC00006$               1          4000           0 DESC

--//只要长度大于2666.降序索引字段都是raw类型,长度都是4000.
--//这个算是特殊情况吧,作为http://blog.itpub.net/267265/viewspace-2667580/的补充.