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

[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/的补充.