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