[20191209]降序索引疑问2.txt

[20191209]降序索引疑问2.txt --//上午写的http://blog.itpub.net/267265/viewspace-2667573/=>[20191209]降序索引疑问.txt --//还有另外的疑问,就是降序索引字段类型的长度的问题.或者讲我以前没注意这个细节. --//通过例子说明: 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 SCOTT@book> create table t (id number(10),vc varchar2(10)); Table created. SCOTT@book> create index if_t_id on t(id desc); Index created. SCOTT@book> create index if_t_vc on t(vc desc); Index created. 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 -------------------- ---------- ----------- -------------- ---------- ID                   NUMBER              22             10          0 VC                   VARCHAR2            10 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_ID    SCOTT       T          SYS_NC00003$               1            34           0 DESC SCOTT       IF_T_VC    SCOTT       T          SYS_NC00004$               1            16           0 DESC --//ID 降序后COLUMN_LENGTH=34. --//vc 降序后COLUMN_LENGTH=16. --//降序索引的字段长度应该比原来的大,因为键值后面要补0xFF(排序的需要).实际上特殊编码如下: --//链接:http://blog.itpub.net/267265/viewspace-2656690/ ascii码                 编码 --------------------------------------------- 0x00                    FEFE 0x0000                  FEFD 0x0001                  FEFC 0x00NN(0xNN>=0x02)      FEFB(注:指前面0x00编码，再次看到这里有点晕) 0x01                    FEFA 0x0100                  FEF9 0x0101                  FEF8         0x01NN(0xNN>=0x02)      FEF7(注:指前面0x01编码) --//理论讲会出现1个字节编码占用2个字节编码的情况.而oracle 降序后字段长度并不是2*原来长度+1. --//自己看上面的编码就很容易仅仅4种编码1个字节编码占用2个字节编码.(0x00,0x00NN,0x01,0x01NN) --//但是不会出现连续2个字节编码占用4个字节编码的情况,最多的可能是2个字节编码占用3个字节编码的情况. --//这样就很容易得出降序后字段长度是 1.5*原来长度+1. SCOTT@book> alter table t  add (vcx  varchar2(9)); Table altered. SCOTT@book> create index if_t_vcx on t(vcx desc); Index created. 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_ID    SCOTT       T          SYS_NC00003$               1            34           0 DESC SCOTT       IF_T_VC    SCOTT       T          SYS_NC00004$               1            16           0 DESC SCOTT       IF_T_VCX   SCOTT       T          SYS_NC00006$               1            15           0 DESC --//9*1.5+1 = 14.5 按大的计算等于15.