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