[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'，要事先處理。
--//我估計許多開發可能根本不知道這個細節。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2985171/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180725]index skip-scan operation.txtIndex
- Oracle vs PostgreSQL Develop（31） - Index Only ScanOracleSQLdevIndex
- [20181201]奇怪的INDEX SKIP SCAN執行計劃.txtIndex
- PostgreSQL DBA(119) - pgAdmin(LIMIT：Index Scan vs Bitmap Index Scan)SQLMITIndex
- 【TUNE_ORACLE】列出走了INDEX FULL SCAN的SQL參考OracleIndexSQL
- 【TUNE_ORACLE】列出走了INDEX SKIP SCAN的SQL參考OracleIndexSQL
- MySQL Index Condition Pushdown(ICP)的使用限制MySqlIndex
- 20180316不使用INDEX FULL SCAN (MIN/MAX)Index
- [20201109]cluvfy comp scan [-verbose].txt
- 簡單談談MySQL的loose index scanMySqlIndex
- C# 使用 Index 和 Range 簡化集合操作C#Index
- python爬蟲 -IndexError: list index out of range報錯Python爬蟲IndexError
- [20210910]table scan相關統計.txt
- 【最佳化】INDEX FULL SCAN (MIN/MAX)訪問路徑Index
- Oracle RAC修改public, VIP, SCAN IPOracle
- [20220124]index split level.txtIndex
- [20220223]Index ITL Limit.txtIndexMIT
- oracle invisible index與unusable index的區別OracleIndex
- oracle rac scan監聽更改埠號Oracle
- Oracle 11G 修改scan_ipOracle
- [20180427]SCAN_IP DNS 反向解析2.txtDNS
- [20231027]Index ITL Limit 2.txtIndexMIT
- [20231027]Index ITL Limit 3.txtIndexMIT
- [20231031]Index ITL Limit 4.txtIndexMIT
- 隨筆：MySQL:eq_range_index_dive_limit 索引下探介面MySqlIndexMIT索引
- Oracle RAC修改Scan IP，Public IP的方法Oracle
- [20181127]12c Advanced Index Compression.txtIndex
- [20190920]Asynchronous Global Index Maintenance and Recycle Bin.txtIndexAINaN
- Oracle分割槽表基礎運維-07增加分割槽(6RANGE_RANGE)Oracle運維
- 關於Pyinstaller在打包Streamlit程式時遇到的IndexError:tuple index out of rangeIndexError
- 說說C# 8.0 新增功能Index和Range的^0是什麼？C#Index
- Oracle 11g RAC SCAN ip的原理及配置Oracle
- oracle rac的scan監聽狀態Not All Endpoints RegisteredOracle
- [20181127]12c Advanced Index Compression 2.txtIndex
- [20190311]12cR2 Advanced index compression.txtIndex
- [20180608]Wrong Results with IOT, Added Column and Secondary Index.txtIndex
- [20220111]完善tpt ashash_index_helper指令碼.txtIndex指令碼
- [20211231]ORA-01418 specified index does not exist.txtIndex