哪位大佬帮忙协助分析一下regexp_substr和substr函数在11g和19c的表现问题：
DROP TABLE dba_man.REG_TEST;
DROP TABLE dba_man.REG_TEST2;
create table dba_man.reg_test(x int,y varchar2(20));
insert into dba_man.reg_test values (1,'1a2a3a4a');
insert into dba_man.reg_test values (2,'5a6a7a8a');
insert into dba_man.reg_test values (3,'9a10a11a12a');
insert into dba_man.reg_test values (4,'13a14a15a16a');
insert into dba_man.reg_test values (5,'17a18a19a20a');
alter table dba_man.reg_test add primary key (x);
create table dba_man.reg_test2(y varchar2(20));
insert into dba_man.reg_test2 values ('1');
insert into dba_man.reg_test2 values ('2');
insert into dba_man.reg_test2 values ('3');
insert into dba_man.reg_test2 values ('4');
insert into dba_man.reg_test2 values ('5');
insert into dba_man.reg_test2 values ('6');
insert into dba_man.reg_test2 values ('7');
insert into dba_man.reg_test2 values ('8');
insert into dba_man.reg_test2 values ('9');
insert into dba_man.reg_test2 values ('10');
insert into dba_man.reg_test2 values ('11');
insert into dba_man.reg_test2 values ('12');
insert into dba_man.reg_test2 values ('13');
insert into dba_man.reg_test2 values ('14');
insert into dba_man.reg_test2 values ('15');
insert into dba_man.reg_test2 values ('16');
insert into dba_man.reg_test2 values ('17');
insert into dba_man.reg_test2 values ('18');
insert into dba_man.reg_test2 values ('19');
insert into dba_man.reg_test2 values ('20');
alter table dba_man.reg_test2 add primary key (y);
test sql：
alter session set "_optimizer_mjc_enabled"=false;
select * from dba_man.reg_test where regexp_substr(y,'[0-9]+',1,rownum) in (select y from dba_man.reg_test2);
select substr(y,1,rownum),t1.* from dba_man.reg_test t1 where substr(y,1,rownum) in (select y from dba_man.reg_test2 T2);
11g:
| 4 | TABLE ACCESS FULL| REG_TEST | 5 | 125 | 3 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN| SYS_C0088536 | 20 | 240 | 0 (0)| 00:00:01 |
19c:
| 4 | TABLE ACCESS FULL| REG_TEST | 5 | 125 | 3 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | SYS_C0017807 | 20 | 240 | 1 (0)| 00:00:01 |
问题一：
11g中：
regexp_substr和substr中含rownum时，索引走INDEX UNIQUE SCAN；
19c中：
regexp_substr和substr中含rownum时，索引走INDEX FULL SCAN ；
问题2：
substr中含rownum时，11g和19c出来的结果集不一致。11g的出来2条，感觉是对的；19c的出来5条，感觉是错的。
11g执行结果：
select substr(y,1,rownum),t1.* from dba_man.reg_test t1 where substr(y,1,rownum) in (select y from dba_man.reg_test2 T2);
SUBSTR(Y,1,ROWNUM) X Y
-------------------------------------------------------------------------------- ---------- --------------------
1 1 1a2a3a4a
13 4
19c执行结果：
select substr(y,1,rownum),t1.* from dba_man.reg_test t1 where substr(y,1,rownum) in (select y from dba_man.reg_test2 T2);
SUBSTR(Y,1,ROWNUM) X Y
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- ------------------------------------------------------------
1 1 1a2a3a4a
13 4 13a14a15a16a
17a 5 17a18a19a20a
5a6a 2 5a6a7a8a
9a10a 3 9a10a11a12a
对于问题2：
19c的这条sql
select /*+ OPT_PARAM('_optimizer_mjc_enabled','false') */
substr(y, 1, rownum), t1.*
from reg_test t1
where substr(t1.y, 1, rownum) in (select t2.y from reg_test2 T2);
复制其实是按下面这个样子执行的
select /*+ OPT_PARAM('_optimizer_mjc_enabled','false') */
substr(y, 1, rownum), t1.*
from reg_test t1
where substr(t1.y, 1, (select rownum from dual)) in (select t2.y from reg_test2 T2);
复制也就是说，条件中函数的参数里如果有rownum，rownum就等于1
你可以尝试删掉一行记录再测试查询，来验证我的说法
delete reg_test2 where y='9';
复制这个问题其实和我这个月写的一篇文章有相似之处，就是rownum在某些时候会直接认为是等于1，而不是查询数据的行数。
当rownum被函数嵌套作为查询条件时，存在一个矛盾点，就是rownum永远是在数据查询出来后，才知道有多少行，才能得到rownum列的值，这和where条件中的函数执行起了冲突，因为得到rownum的值后，传入函数，可能又不满足条件了，因此11g的处理行为是存在歧义的,相较而言，19c处理的结果则不存在歧义。
另外，不用_optimizer_mjc_enabled=false,你会发现11g里查出来只有1行了，数据行数变了;而19c不管加不加_optimizer_mjc_enabled=false，查询出来的数据条目数都不会变化。
在某些情况下hint或者参数的调整并不一定会生效，无论使用什么执行计划，查询出来的结果应该都要一致。因此，11g改个hint就导致数据查询结果都不同，可以理解为是BUG了
评论
有用 0
