[20230908]Oracle Index Range Scan with LIKE Condition on Wildcard '_'.txt

[20230908]Oracle Index Range Scan with LIKE Condition on Wildcard '_'.txt --//昨天看链接：http://ksun-oracle.blogspot.com/2023/09/oracle-index-range-scan-with-like.html，当时一下子没有反应过来， --//作者这样查询怎么会有这么大区别呢？仔细看题目才明显原来查询的字符串里面存在_,解析为任何字符，这样索引的扫描范围不同， --//导致出现逻辑读很大的不同。我重复测试看看。 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 2.建立测试例子： --//drop table test_tab purge; create table test_tab (     id           number    ,grp          number    ,tstr         varchar2(14)    ,name1        varchar2(100)    ,name2        varchar2(100)    ,name3        varchar2(100) ); create unique index test_tab#p on test_tab(id); alter table test_tab add constraint test_tab#p primary key (id); create index test_tab#i#name1 on test_tab (name1); create index test_tab#i#name2 on test_tab (name2); create index test_tab#i#name3 on test_tab (name3); --//作者建立索引的风格与我的不同。   insert into test_tab with sq as ( select level id, mod(level, 300) grp       ,to_char((date'2021-11-22' + rownum/86400), 'YYYYMMDDHH24MISS')           ts       ,decode(mod(level, 3), 0, 'ONE_PART', 1, 'TWO_PART', 2, 'THREE_PART')     part   from dual connect by level <= 3*1e5) select id, grp, ts       ,part ||'.'||grp  ||'.'||ts     name1       ,grp  ||'.'||part ||'.'||ts     name2       ,ts   ||'.'||part ||'.'||grp    name3  from sq; commit; exec dbms_stats.gather_table_stats('', 'TEST_TAB', cascade=>true); col name1 for a30 new_value n1 col name2 for a30 new_value n2 col name3 for a30 new_value n3 SCOTT@test01p> select id, name1, name2, name3 from test_tab m where id = trunc(dbms_random.value(1, 3*1e5));         ID NAME1                          NAME2                          NAME3 ---------- ------------------------------ ------------------------------ ------------------------------     244018 TWO_PART.118.20211124194658    118.TWO_PART.20211124194658    20211124194658.TWO_PART.118 --//主：3个字符串3种风格。最后一个顺序递增，并且字符串_出现的位置最靠后。 select count(*)  lect count(*)       ,count(distinct substr(name1, 1, instr(name1, '_')-1)) name1_prefix_cntx_cnt       ,count(distinct substr(name2, 1, instr(name2, '_')-1)) name2_prefix_cntx_cnt       ,count(distinct substr(name3, 1, instr(name3, '_')-1)) name3_prefix_cntx_cnt   from test_tab;   COUNT(*) NAME1_PREFIX_CNT NAME2_PREFIX_CNT NAME3_PREFIX_CNT ---------- ---------------- ---------------- ----------------     300000                3              300           300000 --//字符'_'出现的位置前面重复的部分计数如上。 create or replace procedure test_tab_proc (p_case number, p_cnt number) as   l_start       number;   l_start_cr    number;   l_end_cr      number;   l_name1       varchar2(100);   l_name2       varchar2(100);   l_name3       varchar2(100);   l_ret         varchar2(200); begin   select name1, name2, name3 into l_name1, l_name2, l_name3     from test_tab where id = trunc(dbms_random.value(1, 3*1e5));   l_start  := dbms_utility.get_time;   select value into l_start_cr from v$mystat s, v$statname n where s.statistic#=n.statistic# and name = 'consistent gets';   case p_case     when 1 then       for i in 1..p_cnt loop         select  /*+ index_rs_asc(t (name1)) */ name1 into l_ret from test_tab t where name1 like l_name1;       end loop;       dbms_output.put_line('--------- Index: name1 like '||l_name1||' --------- ');     when 2 then       for i in 1..p_cnt loop         select  /*+ index_rs_asc(t (name2)) */ name2 into l_ret from test_tab t where name2 like l_name2;       end loop;       dbms_output.put_line('--------- Index: name2 like '||l_name2||' --------- ');     when 3 then       for i in 1..p_cnt loop         select  /*+ index_rs_asc(t (name3)) */ name3 into l_ret from test_tab t where name3 like l_name3;       end loop;       dbms_output.put_line('--------- Index: name3 like '||l_name3||' --------- ');     end case;     select value into l_end_cr from v$mystat s, v$statname n where s.statistic#=n.statistic# and name = 'consistent gets';     dbms_output.put_line('Total Elapsed MS = '||round((dbms_utility.get_time-l_start)*10)||                        ', Total CR gets= '    ||(l_end_cr-l_start_cr)||                        ', Per Exec MS = '     ||round((dbms_utility.get_time-l_start)*10/p_cnt, 2)||                        ', Per Exec CR gets ='||round((l_end_cr-l_start_cr)/p_cnt)); end; / 3.测试： SCOTT@test01p> @ init SCOTT@test01p> set serveroutput on SCOTT@test01p> alter session set tracefile_identifier = 'sql_trc_1'; Session altered. SCOTT@test01p> alter session set events '10046 trace name context forever, level 12'; Session altered. SCOTT@test01p> exec test_tab_proc(1, 100); --------- Index: name1 like THREE_PART.167.20211122130747 --------- Total Elapsed MS = 4210, Total CR gets= 97503, Per Exec MS = 42.1, Per Exec CR gets = 975 PL/SQL procedure successfully completed. SCOTT@test01p> exec test_tab_proc(2, 100); --------- Index: name2 like 49.TWO_PART.20211122044049 --------- Total Elapsed MS = 100, Total CR gets= 1203, Per Exec MS = 1, Per Exec CR gets = 12 PL/SQL procedure successfully completed. SCOTT@test01p> exec test_tab_proc(3, 100); --------- Index: name3 like 20211122023738.THREE_PART.158 --------- Total Elapsed MS = 40, Total CR gets= 303, Per Exec MS = .4, Per Exec CR gets = 3 PL/SQL procedure successfully completed. SCOTT@test01p> set serveroutput off --//与他的测试基本一致。 SCOTT@test01p> select index_name, clustering_factor, leaf_blocks, blevel from dba_indexes v where table_name = 'TEST_TAB'; INDEX_NAME           CLUSTERING_FACTOR LEAF_BLOCKS     BLEVEL -------------------- ----------------- ----------- ---------- TEST_TAB#P                        4717         562          1 TEST_TAB#I#NAME1                299999        2765          2 TEST_TAB#I#NAME2                299999        2773          2 TEST_TAB#I#NAME3                  4717        1478          2 --//作者的解析： Before '_', there are 3 different values ('ONE','TWO','THREE'). So name1 is divided into three parts. TEST_TAB#I#NAME1 has 2766 leaf blocks, TEST_TAB#I#NAME1 index range scan makes 2766/3, which is 902 CR gets per execution (it performs like an index partition full scan). 4. Index Meta Data SCOTT@test01p> select object_name, object_id, to_char(object_id, 'xxxxxxxx') object_id_hex from dba_objects t where object_name like 'TEST_TAB#%'; OBJECT_NAME                     OBJECT_ID OBJECT_ID ------------------------------ ---------- --------- TEST_TAB#I#NAME1                    30257      7631 TEST_TAB#I#NAME2                    30258      7632 TEST_TAB#I#NAME3                    30259      7633 TEST_TAB#P                          30256      7630 SCOTT@test01p> select column_name, avg_col_len from  dba_tab_columns where table_name = 'TEST_TAB'; COLUMN_NAME          AVG_COL_LEN -------------------- ----------- ID                             5 GRP                            4 TSTR                          15 NAME1                         29 NAME2                         29 NAME3                         29 6 rows selected. SCOTT@test01p> select segment_name, header_file, header_block from dba_segments t where segment_name like 'TEST_TAB#%'; SEGMENT_NAME                   HEADER_FILE HEADER_BLOCK ------------------------------ ----------- ------------ TEST_TAB#P                              11         3874 TEST_TAB#I#NAME1                        11         3882 TEST_TAB#I#NAME2                        11         3890 TEST_TAB#I#NAME3                        11         3898 alter session set events 'immediate trace name treedump level 30257'; alter session set events 'immediate trace name treedump level 30258'; alter session set events 'immediate trace name treedump level 30259'; --// index TEST_TAB#I#NAME1 ----- begin tree dump branch: 0x2c00f2b 46141227 (0: nrow: 16, level: 2)    branch: 0x2c010fe 46141694 (-1: nrow: 162, level: 1)       leaf: 0x2c00f2c 46141228 (-1: row:107.107 avs:4041)       leaf: 0x2c01d30 46144816 (0: row:108.108 avs:4004) ....       leaf: 0x2c056bb 46159547 (164: row:136.136 avs:2832) ----- end tree dump --// index TEST_TAB#I#NAME2 ----- begin tree dump branch: 0x2c00f33 46141235 (0: nrow: 17, level: 2)    branch: 0x2c011d3 46141907 (-1: nrow: 149, level: 1)       leaf: 0x2c00f34 46141236 (-1: row:107.107 avs:4041)       leaf: 0x2c01c7f 46144639 (0: row:108.108 avs:4004) ...       leaf: 0x2c056bb 46159547 (164: row:136.136 avs:2832) ----- end tree dump --// index TEST_TAB#I#NAME3 ----- begin tree dump branch: 0x2c00f3b 46141243 (0: nrow: 5, level: 2)    branch: 0x2c024fb 46146811 (-1: nrow: 368, level: 1)       leaf: 0x2c00f3c 46141244 (-1: row:204.204 avs:16) ...       leaf: 0x2c05b0f 46160655 (2: row:203.203 avs:0)       leaf: 0x2c05b10 46160656 (3: row:51.51 avs:5979) ----- end tree dump --//这是因为前面2个索引50-50 分裂，而最后递增顺序插入，这样90-10分裂(实际上最后那条插入的条目在另外的新索引块上) -- index range scan gets index TEST_TAB#I#NAME1 blocks in ordered read (db file sequential read'). --   index range scan first reads index blocks from root block to the left most satisfied first leaf block along branch blocks, --   then reads from first found leaf blocks till last satisfied leaf block (which are linked with each one points to next one). --   One block per read, logically sequential. -- index fast full scan gets index TEST_TAB#I#NAME1 blocks in unordered read ('db file scattered read'). --   index fast full scan reads all index blocks (brach/leaf) like full table scan. --   Multiple blocks per read without considering any order. 5.如果过程改写如下，应该基本相同时间完成。 create or replace procedure test_tab_proc1 (p_case number, p_cnt number) as   l_start       number;   l_start_cr    number;   l_end_cr      number;   l_name1       varchar2(100);   l_name2       varchar2(100);   l_name3       varchar2(100);   l_ret         varchar2(200); begin   select replace(name1,'_','\_'), replace(name2,'_','\_'), replace(name3,'_','\_') into l_name1, l_name2, l_name3     from test_tab where id = trunc(dbms_random.value(1, 3*1e5));   l_start  := dbms_utility.get_time;   select value into l_start_cr from v$mystat s, v$statname n where s.statistic#=n.statistic# and name = 'consistent gets';   case p_case     when 1 then       for i in 1..p_cnt loop         select  /*+ index_rs_asc(t (name1)) */ name1 into l_ret from test_tab t where name1 like l_name1 escape '\' ;       end loop;       dbms_output.put_line('--------- Index: name1 like '||l_name1||' --------- ');     when 2 then       for i in 1..p_cnt loop         select  /*+ index_rs_asc(t (name2)) */ name2 into l_ret from test_tab t where name2 like l_name2 escape '\' ;       end loop;       dbms_output.put_line('--------- Index: name2 like '||l_name2||' --------- ');     when 3 then       for i in 1..p_cnt loop         select  /*+ index_rs_asc(t (name3)) */ name3 into l_ret from test_tab t where name3 like l_name3 escape '\' ;       end loop;       dbms_output.put_line('--------- Index: name3 like '||l_name3||' --------- ');     end case;     select value into l_end_cr from v$mystat s, v$statname n where s.statistic#=n.statistic# and name = 'consistent gets';     dbms_output.put_line('Total Elapsed MS = '||round((dbms_utility.get_time-l_start)*10)||                        ', Total CR gets= '    ||(l_end_cr-l_start_cr)||                        ', Per Exec MS = '     ||round((dbms_utility.get_time-l_start)*10/p_cnt, 2)||                        ', Per Exec CR gets ='||round((l_end_cr-l_start_cr)/p_cnt)); end; / --//注意带入的字符串以及最后的escape. SCOTT@test01p> set serveroutput on SCOTT@test01p> exec test_tab_proc1(1, 100); --------- Index: name1 like ONE\_PART.153.20211122030733 --------- Total Elapsed MS = 140, Total CR gets= 318, Per Exec MS = 1.4, Per Exec CR gets = 3 PL/SQL procedure successfully completed. SCOTT@test01p> exec test_tab_proc1(2, 100); --------- Index: name2 like 16.TWO\_PART.20211123054516 --------- Total Elapsed MS = 90, Total CR gets= 303, Per Exec MS = .9, Per Exec CR gets = 3 PL/SQL procedure successfully completed. SCOTT@test01p> exec test_tab_proc1(3, 100); --------- Index: name3 like 20211123182827.ONE\_PART.207 --------- Total Elapsed MS = 70, Total CR gets= 303, Per Exec MS = .7, Per Exec CR gets = 3 PL/SQL procedure successfully completed. SCOTT@test01p> set serveroutput off --//3个测试基本平均都是3个逻辑读完成。 6.提醒注意： --//这个测试最大的提示就是当采用like查询时，查询使用的绑定变量的字符串有_字符，逻辑读可能出现异常，在实际的应用中应该引 --//起注意。 --//看一个具体的例子: col name1 for a30 new_value n1 col name2 for a30 new_value n2 col name3 for a30 new_value n3 SCOTT@test01p> select id, name1, name2, name3 from test_tab m where id = 1;         ID NAME1                          NAME2                          NAME3 ---------- ------------------------------ ------------------------------ ------------------------------          1 TWO_PART.1.20211122000001      1.TWO_PART.20211122000001      20211122000001.TWO_PART.1 SCOTT@test01p> variable v_name1 varchar2(100); SCOTT@test01p> exec :v_name1 :='TWO_PART.1.20211122000001' PL/SQL procedure successfully completed. SCOTT@test01p> @ sl all alter session set statistics_level = all; Session altered. SCOTT@test01p> select id, name1, name2, name3 from test_tab m where name1 like :v_name1;         ID NAME1                          NAME2                          NAME3 ---------- ------------------------------ ------------------------------ ------------------------------          1 TWO_PART.1.20211122000001      1.TWO_PART.20211122000001      20211122000001.TWO_PART.1 SCOTT@test01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  6952nwup67x5k, child number 0 ------------------------------------- select id, name1, name2, name3 from test_tab m where name1 like :v_name1 Plan hash value: 2100491709 -------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |                  |      1 |        |       |     5 (100)|          |      1 |00:00:00.06 |     898 | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TAB         |      1 |      1 |    92 |     5   (0)| 00:00:01 |      1 |00:00:00.06 |     898 | |*  2 |   INDEX RANGE SCAN                  | TEST_TAB#I#NAME1 |      1 |      1 |       |     3   (0)| 00:00:01 |      1 |00:00:00.06 |     897 | -------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / M@SEL$1    2 - SEL$1 / M@SEL$1 Peeked Binds (identified by position): --------------------------------------    1 - :1 (VARCHAR2(30), CSID=852): 'TWO_PART.1.20211122000001' Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("NAME1" LIKE :V_NAME1)        filter("NAME1" LIKE :V_NAME1) 31 rows selected. --//这样逻辑读高达898. SCOTT@test01p> exec :v_name1 :='TWO\_PART.1.20211122000001' PL/SQL procedure successfully completed. SCOTT@test01p> select id, name1, name2, name3 from test_tab m where name1 like :v_name1 escape '\';         ID NAME1                          NAME2                          NAME3 ---------- ------------------------------ ------------------------------ ------------------------------          1 TWO_PART.1.20211122000001      1.TWO_PART.20211122000001      20211122000001.TWO_PART.1 SCOTT@test01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  byw8j1gn2d0yv, child number 0 ------------------------------------- select id, name1, name2, name3 from test_tab m where name1 like :v_name1 escape '\' Plan hash value: 2100491709 -------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |                  |      1 |        |       |     5 (100)|          |      1 |00:00:00.01 |       5 | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TAB         |      1 |      1 |    92 |     5   (0)| 00:00:01 |      1 |00:00:00.01 |       5 | |*  2 |   INDEX RANGE SCAN                  | TEST_TAB#I#NAME1 |      1 |      1 |       |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 | -------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / M@SEL$1    2 - SEL$1 / M@SEL$1 Peeked Binds (identified by position): --------------------------------------    1 - :1 (VARCHAR2(30), CSID=852): 'TWO\_PART.1.20211122000001' Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("NAME1" LIKE :V_NAME1 ESCAPE '\')        filter("NAME1" LIKE :V_NAME1 ESCAPE '\') 32 rows selected. --//正常的业务很少写like语句使用escape '\'的，即使写了带入的参数也很少写成'TWO\_PART.1.20211122000001'，要事先处理。 --//我估计许多开发可能根本不知道这个细节。