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

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