[20231105]降序索引的疑问.txt

[20231105]降序索引的疑问.txt --//我们生产系统有一套系统我以前维护过,出现一个奇葩现象,建立一堆降序索引,实际上完全没有必要,最后我改了许多索引为普通索引. --//由于可能后续维护或者可能是我遗漏了(当然还有可能索引太大我没有修改),还是有一些索引没改过来. --//我讲过降序索引的一些问题,比如如果索引列是递增的,降序索引可以讲人为导致索引变大,因为这时的分裂变成50-50分裂.而且降序 --//索引包含NULL值,实际上当查询条件出现is null时,根本不会使用索引. --//可以最近我遇到1个问题,通过例子说明: 1.环境: SCOTT@test01p> @ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 SCOTT@test01p> show parameter max_string_size NAME             TYPE    VALUE ---------------- ------- ---------- max_string_size  string  EXTENDED 2.测试: SCOTT@test01p> create table t1 ( id number(10),vc varchar2(10), cr_date date,vl varchar2(4000)); Table created. SCOTT@test01p> @ o2 t1 SCOTT@test01p> @ pr ============================== O_OWNER                       : SCOTT O_OBJECT_NAME                 : T1 O_OBJECT_TYPE                 : TABLE SUBOBJECT_NAME                : O_STATUS                      : VALID OID                           : 30357 D_OID                         : 30357 CREATED                       : 2023-11-05 20:38:33 LAST_DDL_TIME                 : 2023-11-05 20:38:33 PL/SQL procedure successfully completed. SCOTT@test01p> create index if_t1_id on t1(id desc); Index created. SCOTT@test01p> create index if_t1_vc on t1(vc desc); Index created. SCOTT@test01p> create index if_t1_cr_date on t1(cr_date desc); Index created. SCOTT@test01p> create index if_t1_vl on t1(vl desc); Index created. --//建立了4个降序索引. SCOTT@test01p> select OBJ#,COL#,SEGCOL#,SEGCOLLENGTH,name,LENGTH,DEFLENGTH from sys.col$ where obj#='30357';       OBJ#       COL#    SEGCOL# SEGCOLLENGTH NAME         LENGTH  DEFLENGTH ---------- ---------- ---------- ------------ ------------ ------ ----------      30357          1          1           22 ID               22      30357          2          2           10 VC               10      30357          3          3            7 CR_DATE           7      30357          4          4         4000 VL             4000      30357          0          0           34 SYS_NC00005$     34          4      30357          0          0           16 SYS_NC00006$     16          4      30357          0          0           12 SYS_NC00007$     12          9      30357          0          0         6001 SYS_NC00008$   6001          4 8 rows selected. --//注意看SEGCOLLENGTH列,SYS_NC0000N$的长度都比原来大.大致推断SEGCOLLENGTH*1.5+1. --//比如: 7*1.5+1 = 11.5  ,取整就是12. --//实际上很好理解我以前写过降序排序的编码问题.正常情况是编码 与 0xff 异或,最后在结尾加上0xff编码,最后加的主要是排序的需 --//要,这样带来了问题chr(0)的编码就不能是0xff了,这样oracle在一些字符上产生一套不同的编码,并且要考虑排序问题. --//我曾经画过一个表格: ASCII码                 编码 --------------------------------------------- 0x00                    FEFE 0x0000                  FEFD 0x0001                  FEFC 0x00NN(0xNN>=0x02)      FEFB 0x01                    FEFA 0x0100                  FEF9 0x0101                  FEF8         0x01NN(0xNN>=0x02)      FEF7 --------------------------------------------- --//比如 0x0003 的编码就是 FEFB FC. SCOTT@test01p> select SYS_OP_DESCEND(chr(0)||chr(3)),SYS_OP_DESCEND(null) from dual ; SYS_OP_D SY -------- -- FEFBFCFF 00 --//这样索引后为了保证键值不会溢出,函数索引raw类型长度必须是原来的1.5倍+结尾的1个字符(0xff). --//顺便测试is null查询是否会使用索引. SCOTT@test01p> select * from t1 where id is null; no rows selected SCOTT@test01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  bnt89b4qftcd0, child number 0 ------------------------------------- select * from t1 where id is null Plan hash value: 3617692013 --------------------------------------------------------------------------- | Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |        |       |     2 (100)|          | |*  1 |  TABLE ACCESS FULL| T1   |      1 |  2031 |     2   (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("ID" IS NULL) --//你可以发现并不会使用索引.我猜测不是等值的缘故. --//如果你写成如下,可以使用索引的. SCOTT@test01p> select * from t1 where SYS_OP_DESCEND(id)=hextoraw('00'); no rows selected SCOTT@test01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  aucnd2w7hcm3w, child number 0 ------------------------------------- select * from t1 where SYS_OP_DESCEND(id)=hextoraw('00') Plan hash value: 2882164202 ------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |          |        |       |     1 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1       |      1 |  2031 |     1   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN                  | IF_T1_ID |      1 |       |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T1@SEL$1    2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("T1"."SYS_NC00005$"=HEXTORAW('00')) 3.我不知道是否扩展max_string_size=EXTENDED的原因. --//如果在18c或者19c呢? TTT@192.168.2.7:1521/orcl> @ pr ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 18.0.0.0.0 BANNER                        : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 BANNER_LEGACY                 : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. create table t1 ( id number(10),vc varchar2(10), cr_date date,vl varchar2(4000)); create index if_t1_id on t1(id desc); create index if_t1_vc on t1(vc desc); create index if_t1_cr_date on t1(cr_date desc); create index if_t1_vl on t1(vl desc); TTT@192.168.2.7:1521/orcl> @ o2 t1 TTT@192.168.2.7:1521/orcl> @ pr ============================== O_OWNER                       : TTT O_OBJECT_NAME                 : T1 O_OBJECT_TYPE                 : TABLE SEG_PART_NAME                 : O_STATUS                      : VALID OID                           : 427915 D_OID                         : 427915 CREATED                       : 2023-11-07 09:56:12 LAST_DDL_TIME                 : 2023-11-07 09:56:12 PL/SQL procedure successfully completed. TTT@192.168.2.7:1521/orcl> select OBJ#,COL#,SEGCOL#,SEGCOLLENGTH,name,LENGTH,DEFLENGTH from sys.col$ where obj#='427915';       OBJ#       COL#    SEGCOL# SEGCOLLENGTH NAME         LENGTH  DEFLENGTH ---------- ---------- ---------- ------------ ------------ ------ ----------     427915          1          1           22 ID               22     427915          2          2           10 VC               10     427915          3          3            7 CR_DATE           7     427915          4          4         4000 VL             4000     427915          0          0           34 SYS_NC00005$     34          4     427915          0          0           16 SYS_NC00006$     16          4     427915          0          0           12 SYS_NC00007$     12          9     427915          0          0         2000 SYS_NC00008$   2000          4 8 rows selected. --//除了VL字段的降序索引SEGCOLLENGTH=2000,其它一样. --//11g呢?前面步骤略. 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> @ o2 t1 SCOTT@book> @ pr ============================== O_OWNER                       : SCOTT O_OBJECT_NAME                 : T1 O_OBJECT_TYPE                 : TABLE SEG_PART_NAME                 : O_STATUS                      : VALID OID                           : 90380 D_OID                         : 90380 CREATED                       : 2023-11-07 09:59:37 LAST_DDL_TIME                 : 2023-11-07 09:59:37 PL/SQL procedure successfully completed. SCOTT@book> select OBJ#,COL#,SEGCOL#,SEGCOLLENGTH,name,LENGTH,DEFLENGTH from sys.col$ where obj#='90380';       OBJ#       COL#    SEGCOL# SEGCOLLENGTH NAME         LENGTH  DEFLENGTH ---------- ---------- ---------- ------------ ------------ ------ ----------      90380          1          1           22 ID               22      90380          2          2           10 VC               10      90380          3          3            7 CR_DATE           7      90380          4          4         4000 VL             4000      90380          0          0           34 SYS_NC00005$     34          4      90380          0          0           16 SYS_NC00006$     16          4      90380          0          0           12 SYS_NC00007$     12          9      90380          0          0         4000 SYS_NC00008$   4000          4 8 rows selected. --//除了VL字段的降序索引SEGCOLLENGTH=4000.我不知道是否12c我的测试环境配置max_string_size=EXTENDED. --//这样带来的问题是可能一些数据无法插入. --//11G的测试: SCOTT@book> insert into t1(vl) values(lpad('a',3998,'a')||chr(0)||chr(2)); insert into t1(vl) values(lpad('a',3998,'a')||chr(0)||chr(2))             * ERROR at line 1: ORA-01706: user function result value was too large SCOTT@book> insert into t1(vl) values(lpad('a',3999,'a')||chr(0)); insert into t1(vl) values(lpad('a',3999,'a')||chr(0))             * ERROR at line 1: ORA-01706: user function result value was too large --//当然18c插入长度更小. TTT@192.168.2.7:1521/orcl> insert into t1(vl) values(lpad('a',1998,'a')||chr(0)); 1 row created. TTT@192.168.2.7:1521/orcl> insert into t1(vl) values(lpad('a',1999,'a')||chr(0)); insert into t1(vl) values(lpad('a',1999,'a')||chr(0))             * ERROR at line 1: ORA-01706: user function result value was too large TTT@192.168.2.7:1521/orcl> insert into t1(vl) values(lpad(chr(1)||chr(2),1333,chr(1)||chr(2))); 1 row created. TTT@192.168.2.7:1521/orcl> insert into t1(vl) values(lpad(chr(1)||chr(2),1334,chr(1)||chr(2))); insert into t1(vl) values(lpad(chr(1)||chr(2),1334,chr(1)||chr(2)))             * ERROR at line 1: ORA-01706: user function result value was too large TTT@192.168.2.7:1521/orcl> commit ; Commit complete. TTT@192.168.2.7:1521/orcl> select length(vl),lengthb(vl),dump(substr(vl,1,2),16) c20 from t1; LENGTH(VL) LENGTHB(VL) C20 ---------- ----------- --------------------       1999        1999 Typ=1 Len=2: 61,61       1333        1333 Typ=1 Len=2: 1,2 --//特殊情况仅仅插入1333个字符. --//当然很少在这么长的字段建立索引，而且还是降序索引，并且插入的字符串这么特别。 --//实际上这个根本不是问题，只不过我当时看到索引的类型以及长度时没有反应过来，顺便回头看了原来的文档做的测试。 --//感觉这是一种倒推，不知道oracle为什么做这样的修改,限制了降序索引raw类型最大2000。