在 DM 中控制 LIKE 子句优化的参数是 LIKE_OPT_FLAG（默认值为 63，全优化模式），当
然可以在 SQL 中用 HINT 指定 LIKE_OPT_FLAG 的值，让优化器如何处理 LIKE 子句！
通过下边的例子来理解查询转化及 like 的优化。
说明：下边的测试如果不加 LIKE_OPT_FLAG 的 hint，默认是 LIKE_OPT_FLAG=63（全优
化模式）
1)、X%的情况来说，是可以走索引的（只要统计信息等正确）。这是所有的数据库的特
性，包括 DM 在内，--此不受 LIKE_OPT_FLAG 控制。
DROP TABLE TEST;
CREATE TABLE TEST(C1 VARCHAR(10), C2 VARCHAR(200));
insert into TEST select DBMS_RANDOM.string('X', 9), DBMS_RANDOM.string('P', 99) from
dual connect by level<=3000000; --300 万
commit;
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA', 'TEST',TRUE);
--1）、没有在 C1 列上创建索引，走聚簇索引的全表扫描！并且可以看到：like '4%'是被查询转化为 TEST.C1 >=
'4' AND TEST.C1 < '5'；
SQL> explain SELECT * FROM TEST WHERE C1 like '4%';
1 #NSET2: [407, 72720, 104]
2 #PRJT2: [407, 72720, 104]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [407, 72720, 104]; (TEST.C1 >= '4' AND TEST.C1 < '5')
4 #CSCN2: [407, 3000000, 104]; INDEX33556411(TEST)
--2）在 C1 列上创建索引，走正确的索引扫描！【注意：如果没有正确的走如下的计划，则可能的原因是：1、统计信
息不准；2、发生了隐式转化（比如表字段 C1 为 int 型，而这里的 like 字名是字符型）3、where 条件中形如 col
like ‘%X’，在 col 列上没有 reverse()函数索引】
create index index_TEST_1 on test(C1);
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA', 'TEST',TRUE);
SQL> explain SELECT * FROM TEST WHERE C1 like '4%';--
1 #NSET2: [75, 68580, 104]
2 #PRJT2: [75, 68580, 104]; exp_num(3), is_atom(FALSE)
3 #BLKUP2: [75, 68580, 104]; INDEX_TEST_1(TEST)
4 #SSEK2: [75, 68580, 104]; scan_type(ASC), INDEX_TEST_1(TEST),
scan_range['4','5')
--3）、关闭 LIKE_OPT_FLAG 参数，也一样的会正确的走索引扫描！
SQL> explain SELECT /*+LIKE_OPT_FLAG(0) */ * FROM TEST WHERE C1 like '4%';
1 #NSET2: [75, 68580, 104]
2 #PRJT2: [75, 68580, 104]; exp_num(3), is_atom(FALSE)
3 #BLKUP2: [75, 68580, 104]; INDEX_TEST_1(TEST)
4 #SSEK2: [75, 68580, 104]; scan_type(ASC), INDEX_TEST_1(TEST),
scan_range['4','5')2）、对于%X 的情况来说（即：like 子句中首部存在通配符，并且条件列存在 REVERSE()
函数索引，达梦是优化为 REVERSE()函数），在 DM 中要让其走索引，可以在列上建
reverse()函数索引,并 ALTER SESSION SET 'STR_LIKE_IGNORE_MATCH_END_SPACE'=0；
例如：
DROP TABLE TEST;
CREATE TABLE TEST(C1 VARCHAR(100), C2 VARCHAR(1000));insert into TEST select DBMS_RANDOM.string('A', 99), DBMS_RANDOM.string('X', 999) from
dual connect by level<=1000000;
commit;
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA', 'TEST',TRUE);
--1）、先不创建创建索引，可以看出是走聚簇索引的全表扫描！，并且优化器没的作任何子句的改动！
SQL> explain SELECT * FROM TEST WHERE C1 like '%4';
1 #NSET2: [135, 50000, 104]
2 #PRJT2: [135, 50000, 104]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [135, 50000, 104]; TEST.C1 LIKE '%4'
4 #CSCN2: [135, 1000000, 104]; INDEX33556413(TEST)
--2）、若列 C1 上建立了一个 reverse()函数索引：
CREATE INDEX IDX_TEST_C1 ON TEST(REVERSE(C1)) online;
此时还需要设置 INI 参数 STR_LIKE_IGNORE_MATCH_END_SPACE = 0，指定在 LIKE
运算中不忽略匹配串的结尾 0，否则优化器不会使用 reverse()函数索引。
ALTER SESSION SET 'STR_LIKE_IGNORE_MATCH_END_SPACE'=0;
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA', 'TEST',TRUE); 可以看到：用就用系统默认的默认是 LIKE_OPT_FLAG=63 即可（即不用带 HINT）都会
走正确的二级索引！从 ET 可以看出第二个和第三个执行计划（在 SLCT2 节点：exp11 like
‘cba%’就相当于 REVERSE(TEST.C1)”）的时间是都差不多的。
测试 drop 掉 reverse()函数索引，可以看到它们都走不了索引！！！TEST.C1 LIKE '%abc'。
3）、对于%X%的情况来说（即：对于 LIKE 表达式首尾存在通配符的情况，优化为
POSITION()函数；）不能走索引，即使建立 REVERSE()函数索引也不能走索引！只能做
LIKE 子句的优化（即：转化为 POSITION()函数）。
DROP TABLE TEST;
CREATE TABLE TEST(C1 VARCHAR(10), C2 VARCHAR(200));
insert into TEST select DBMS_RANDOM.string('X', 9), DBMS_RANDOM.string('P', 99) from
dual connect by level<=3000000; --300 万
commit;
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA', 'TEST',TRUE);
--1）、没有在 C1 列上创建索引，走聚簇索引的全表扫描！并且可以看出将 LIKE 表达式优化成了 POSITION()函数
进行处理，此执行计划中的 exp11 即对应 POSITION(4,TEST.C1)，
SQL> explain SELECT * FROM TEST WHERE C1 like '%4%';
1 #NSET2: [407, 150000, 104]
2 #PRJT2: [407, 150000, 104]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [407, 150000, 104]; exp11 > 0
4 #CSCN2: [407, 3000000, 104]; INDEX33556414(TEST)
--2）在 C1 列上创建索引（注不是 reverse()函数索引），则不能走二级索引扫描！【注意：如果没有正确的走如下
的计划，则可能的原因是：1、统计信息不准；2、发生了隐式转化（比如表字段 C1 为 int 型，而这里的 like 字名是
字符型）、3、where 条件中形如 col like ‘%X’，在 col 列上没有 reverse()函数索引】
create index index_TEST_1 on test(C1);
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA', 'TEST',TRUE);
SQL> explain SELECT * FROM TEST WHERE C1 like '%4%';--可以看到在 C1 列上创建索引（注不是
reverse()函数索引），不能正确的走二级索引扫描！
1 #NSET2: [407, 150000, 104]
2 #PRJT2: [407, 150000, 104]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [407, 150000, 104]; exp11 > 0
4 #CSCN2: [407, 3000000, 104]; INDEX33556414(TEST)--当然这里可以 hint 强制走，但是可以看出：COST 很高的！所以不能用。
SQL> explain SELECT /*+INDEX(TEST, INDEX_TEST_1) */* FROM TEST WHERE C1 like '%4%';
1 #NSET2: [3275, 150000, 104]
2 #PRJT2: [3275, 150000, 104]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [3275, 150000, 104]; exp11 > 0
4 #BLKUP2: [3275, 3000000, 104]; INDEX_TEST_1(TEST)
5 #SSCN: [3275, 3000000, 104]; INDEX_TEST_1(TEST)
--3）在 C1 列上创建 reverse()函数索引，能不能让其走正确的二级索引扫描？答案是不能走的哈！！！
drop index index_TEST_1;
CREATE INDEX IDX_TEST_C1 ON TEST(REVERSE(C1)) online;
ALTER SESSION SET 'STR_LIKE_IGNORE_MATCH_END_SPACE'=0;
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA', 'TEST',TRUE);
SQL> explain SELECT * FROM TEST WHERE C1 like '%4%';--可以看到仍走不了正确的二级索引！只能做
like 表达式的优化 POSITION()；exp11 > 0
1 #NSET2: [407, 150000, 104]
2 #PRJT2: [407, 150000, 104]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [407, 150000, 104]; exp11 > 0
4 #CSCN2: [407, 3000000, 104]; INDEX33556414(TEST)
--hint 强制走，但是可以看出：COST 很高的！所以不能用。
SQL> explain SELECT /*+INDEX(TEST, IDX_TEST_C1) */ * FROM TEST WHERE C1 like '%ABC%';
1 #NSET2: [3275, 3000000, 104]
2 #PRJT2: [3275, 3000000, 104]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [3275, 3000000, 104]; exp11 > 0
4 #BLKUP2: [3275, 3000000, 104]; IDX_TEST_C1(TEST)
5 #SSCN: [3275, 3000000, 104]; IDX_TEST_C1(TEST)
--加 LIKE_OPT_FLAG 参数的 HINT 也一样。
SQL> explain SELECT /*+LIKE_OPT_FLAG(1) */ * FROM TEST WHERE C1 like '%4%';
1 #NSET2: [407, 150000, 104]
2 #PRJT2: [407, 150000, 104]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [407, 150000, 104]; exp11 > 0
4 #CSCN2: [407, 3000000, 104]; INDEX33556414(TEST)
used time: 1.256(ms). Execute id is 0.
SQL> explain SELECT /*+LIKE_OPT_FLAG(1) */ * FROM TEST WHERE C1 like '%ABC%';
1 #NSET2: [407, 150000, 104]
2 #PRJT2: [407, 150000, 104]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [407, 150000, 104]; exp11 > 0
4 #CSCN2: [407, 3000000, 104]; INDEX33556414(TEST)4）、在 DM 中默认对于 COL1 LIKE COL2 || '%'的情况，会优化为 POSITION()函数；
总结：
1、X%的情况来说：是可以走索引的；如果走了全表扫描，语句将会被改写（此语句为最好的）！
2、%X 的情况来说：通过列上建立 reverse()函数索引，也是可以走索引的；如果走了全表扫描，
语句将不会被改写！
3、%X%的情况来说：不能走索引，即使建立 REVERSE()函数索引也不能走索引！只能做 LIKE 子句的优化（即：转化为 POSITION()函数）。
更多学习内容参考：达梦数据库 - 新一代大型通用关系型数据库 | 达梦云适配中心 https://eco.dameng.com
3325
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
