[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.