[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呢?
[email protected]: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);
[email protected]:1521/orcl> @ o2 t1
[email protected]: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.
[email protected]: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插入长度更小.
[email protected]:1521/orcl> insert into t1(vl) values(lpad('a',1998,'a')||chr(0));
1 row created.
[email protected]: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
[email protected]:1521/orcl> insert into t1(vl) values(lpad(chr(1)||chr(2),1333,chr(1)||chr(2)));
1 row created.
[email protected]: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
[email protected]:1521/orcl> commit ;
Commit complete.
[email protected]: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。