Shulou(Shulou.com)06/01 Report--

1. Overview

# like (1) when using like query, followed by fuzzy matching, then walk the index, such as like 'test%' (2) when using like query, the front fuzzy match does not walk the index, such as like'% test'# does not walk the index because it is not equal to, that is, equal to a large amount of data, so it does not walk the index # implicit conversion, when occurs in the index column, does not walk the index, occurs in the conditional value column Walk the index (1) if the implicit conversion occurs in the value column, then walk the index, for example, when the query uses a date query, select * from test_implic where bir_date = '20180122 14 22 15 32' (2) if the index column is implicitly converted, do not walk the index, such as the column data type is varchar2, use the following query select bir_date from test_implic where id = 2000; (3) when the number column is equal to the string, walk the index 2. test

(1) Fuzzy matching after like, fuzzy matching before index like walking the whole table

# create test table create table test_bind (id number,name varchar2 (20)); # insert data declarei number;beginfor I in 1..100000loopinsert into test_bind values (iGraingha'); end loop;end;/declarei number;beginfor I in 100000..100010loopinsert into test_bind values (iGrainechast`); end loop;end;/# create index create index IDX_TEST_BIND on test_bind (name) # collect statistics exec dbms_stats.gather_table_stats ('LIBAI','TEST_BIND'); # query, after fuzzy matching, you can see that index LIBAI@honor1 > set autotrace onLIBAI@honor1 > select * from test_bind where name like' te%' ID NAME---100001 Test 100002 test 100003 test 100004 test 100005 test 100006 test 100007 test 100008 test 100009 test 100010 test10 rows selected.Elapsed: 00:00:00.00Execution Plan---Plan hash value: 2889536435- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) ) | Time |-| 0 | SELECT STATEMENT | | 9 | 90 | 3 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | TEST_BIND | 9 | 90 | 3 (0) | 00:00:01 | | * 2 | INDEX RANGE SCAN | IDX_TEST_BIND | 9 | | 2 (0) | 00:00:01 |- -Predicate Information (identified by operation id):- -2-access ("NAME" LIKE 'te%') filter ("NAME" LIKE' te%') Statistics--- 1 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 782 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed # before Fuzzy matching You can see that the full table scan LIBAI@honor1 > select * from test_bind where name like'% st' ID NAME---100001 Test 100002 test 100003 test 100004 test 100005 test 100006 test 100007 test 100008 test 100009 test 100010 test10 rows selected.Elapsed: 00:00:00.02Execution Plan---Plan hash value: 3519963602- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 5001 | 50010 | 69 (2) | 00:00:01 | | * 1 | TABLE ACCESS FULL | TEST_BIND | 5001 | 50010 | 69 (2) | 00:00: 01 |-Predicate Information (identified by operation id):- -1-filter ("NAME" LIKE'% st' AND "NAME" IS NOT NULL) Statistics--- 1 recursive calls 0 db block gets 236 consistent gets 0 physical reads 0 redo size 734 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed

(2) do not walk the index

LIBAI@honor1 > select * from test_bind where name 'test' ID NAME---100001 Test 100002 test 100003 test 100004 test 100005 test 100006 test 100007 test 100008 test 100009 test 100010 test10 rows selected.Elapsed: 00:00:00.01Execution Plan---Plan hash value: 3519963602- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -- | 0 | SELECT STATEMENT | | 18 | 180 | 69 (2) | 00:00:01 | | * 1 | TABLE ACCESS FULL | TEST_BIND | 18 | 180 | 69 (2) | 00:00:01 |-Predicate Information (identified by operation id): -1-filter ("NAME" '') Statistics--- 1 recursive calls 0 db block gets 236 consistent gets 0 physical reads 0 redo size 734 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed

(3) implicit conversion

# construct a test environment

Create table test_implic (id varchar2 (20), name varchar2 (20), bir_date date default sysdate); declarei varchar2 (10); beginfor i in 1..10000loopinsert into test_implic values (iMagnum czhzhzhzhzhong Sysdate); end loop;commit;end;/create index idx_test_implic_id on test_implic (id); create index idx_test_implic_bir_date on test_implic (bir_date); exec dbms_stats.gather_table_stats ('LIBAI','TEST_IMPLIC')

# when the varchar2 type is equal to a number, do not walk the index

LIBAI@honor1 > select bir_date from test_implic where id = 2000 BIR_DATE-2020-01-19 20:00:51Execution Plan---Plan hash value: 965190314Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -| 0 | SELECT STATEMENT | | 1 | 13 | 11 (0) | 00:00:01 | | * 1 | TABLE ACCESS FULL | TEST_IMPLIC | 1 | 13 | 11 (0) | 00:00:01 |- -Predicate Information (identified by operation id):- -1-filter (TO_NUMBER ("ID") = 2000) Statistics--- 1 recursive calls 0 db block gets 38 consistent gets 0 physical reads 0 redo size 531 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedLIBAI@honor1 > select bir_date from test_implic where id = to_char (2000) BIR_DATE-2020-01-19 20:00:51Execution Plan---Plan hash value: 3908402167 Murray- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0) | 00:00:01 | 1 | TABLE ACCESS BY INDEX ROWID | TEST_IMPLIC | 1 | 13 | 2 (0) | 00:00:01 | * 2 | INDEX RANGE SCAN | IDX_TEST_IMPLIC_ID | 1 | | 1 (0) | 00:00:01 |- -Predicate Information (identified by operation id):- -2-access ("ID" = '2000') Statistics--- 1 recursive calls 0 db block gets 4 consistent gets 4 physical reads 0 redo size 531 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

# when number is equal to a string, walk the index

LIBAI@honor1 > select * from test_bind where id = '1000' ID NAME 1000 Execution Plan---Plan hash value: 2345 277976 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | TEST_BIND | 1 | 10 | 2 (0) | 00:00:01 | | * 2 | INDEX RANGE SCAN | IDX_TEST_BIND_ID | 1 | | 1 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-2-access ("ID" = 1000) Statistics- 14 recursive calls 0 db block gets 33 consistent gets 0 physical reads 0 redo size 595 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts ( Memory) 0 sorts (disk) 1 rows processed

# when the date is equal to the string, take the index

LIBAI@honor1 > select * from test_implic where bir_date = '20180122 1414 1422 from test_implic where bir_date 32' No rows selectedExecution Plan---Plan hash value: 3390782276 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | TEST_IMPLIC | 1 | 17 | 2 (0) | 00:00:01 | | * 2 | INDEX RANGE SCAN | IDX_TEST_IMPLIC_BIR_DATE | 1 | | 1 (0) | 00:00:01 |- -Predicate Information (identified by operation id):- -2-access ("BIR_DATE" = '20180122 14 recursive calls 22 Statistics--- 32') recursive calls 0 db block gets 2 consistent gets 4 physical reads 0 redo size 466 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.