[20231130]降序索引与数据类型narchar2.txt

--//昨天看了链接  https://jonathanlewis.wordpress.com/2023/11/27/sys_op_descend/.
--//我一直认为输入字符串里面不大可能存在chr(0)的情况,但是我错了.如果你采用nvarchar2类型,在此字段上建立降序索引,就有可能
--//遇到这样的情况.我仅仅重复作者的测试:

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.建立测试例子:

create table t1a (
nva nvarchar2(128),
nvd nvarchar2(128),
va  varchar2(128),
vd  varchar2(128)
);

insert into t1a select object_name, object_name, object_name, object_name from all_objects where rownum <= 50000 ;

create index t1a_nva on t1a(nva);
create index t1a_nvd on t1a(nvd desc);
create index t1a_va on t1a(va);
create index t1a_vd on t1a(vd desc);

execute dbms_stats.gather_table_stats(user,'t1a')

SCOTT@book> select index_name, leaf_blocks from user_indexes where table_name = 'T1A' order by index_name;
INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
T1A_NVA                                418
T1A_NVD                                592
T1A_VA                                 251
T1A_VD                                 258

--//可以发现T1A_NVD的索引比T1A_NVA大。

SCOTT@book> select column_name, avg_col_len from user_tab_cols where table_name = 'T1A' order by column_name;
COLUMN_NAME          AVG_COL_LEN
-------------------- -----------
NVA                           49
NVD                           49
SYS_NC00005$                  74
SYS_NC00006$                  26
VA                            25
VD                            25
6 rows selected.