oracle 更新 是否影响查询,ORACLE 执行计划会不会影响查询结果

在读这篇文章时请先问自己两个问题

1、查询语句中，select部分字段的增减会不会影响结果条数？

2、ORACLE 执行计划会不会影响查询结果？

下面开始实验

新建一个测试表

点击(此处)折叠或打开

create table E_USERTYPE

(

USERTYPE_CODE VARCHAR2(50),

USERTYPE_NAME VARCHAR2(100)

)

插入几条记录

点击(此处)折叠或打开

insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)

values ('01', '第一产业用电');

insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)

values ('02', '第二产业用电');

insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)

values ('03', '第三产业用电');

insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)

values ('04', '居民生活用电');

insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)

values ('05', '工业用电');

insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)

values ('99', '全社会用电');

我的目标是从一个代码表中随机得到一条记录。思路是随机出一个1~ 代码表总行数间的一个数字。使用with as  得到两个视图进行关联，如下

点击(此处)折叠或打开

--为原表加一个行号

with base as (select usertype_code,rownum line from e_usertype ),

--随机出一个1到 e_usertype 总行数的数字

baseLineas(selectfloor(DBMS_RANDOM.VALUE(1,(selectcount(*)frome_usertype)))linefromdual)

--两个虚表进行关联，随机得到一条记录

select t.usertype_code from base t,baseLine t1 where t.line = t1.line

但执行上面语句是发现问题，它不能实现随机出一条记录，它的结果有时是多条，更多时候是一条都没有。当我改变一个查询语句的字段时，情况发生了变化。

如下,仅仅是在最后的查询语句中加了一个t1.line 这个字段，结果就正常了，每次执行都可以随机得到一条记录。

点击(此处)折叠或打开

--为原表加一个行号

with base as (select usertype_code,rownum line from e_usertype ),

--随机出一个1到 e_usertype 总行数的数字

baseLine as (select floor(DBMS_RANDOM.VALUE(1,(select count(*) from e_usertype))) line from dual)

--两个虚表进行关联，随机得到一条记录

select t.usertype_code,t1.line from base t,baseLine t1 where t.line = t1.line

到此有点崩溃，因为如果有人问我select 字段的增减会不会影响结果条数，我肯定会回答：不会！但这个测试，我真实的看到了增加了一个t1.line 字段，结果就不一样了。

遇到问题，就分析问题吧。脑中马上想到的就是：执行计划不同了！

在此之前，我一直坚信oracle使用hint 或者其它方式改变执行计划是不会影响查询结果。因为如果指定的执行计划会影响到查询结果，那么这个hint 就会被无视。

那看一下上面两条语句的执行计划吧

点击(此处)折叠或打开

--为原表加一个行号

with base as (select usertype_code,rownum line from e_usertype ),

--随机出一个1到 e_usertype 总行数的数字

baseLine as (select floor(DBMS_RANDOM.VALUE(1,(select count(*) from e_usertype))) line from dual)

--两个虚表进行关联，随机得到一条记录

select t.usertype_code from base t,baseLine t1 where t.line = t1.line

点击(此处)折叠或打开

--为原表加一个行号

with base as (select usertype_code,rownum line from e_usertype ),

--随机出一个1到 e_usertype 总行数的数字

baseLine as (select floor(DBMS_RANDOM.VALUE(1,(select count(*) from e_usertype))) line from dual)

--两个虚表进行关联，随机得到一条记录

select t.usertype_code,t1.line from base t,baseLine t1 where t.line = t1.line

由此想到，如果加上 /*+ materialize */ 会不会有效，测试一把，果然正常了。

点击(此处)折叠或打开

with base as

(select usertype_code, rownum line from e_usertype),

baseLine as

(select /*+ materialize */

floor(DBMS_RANDOM.VALUE(1, (select count(*) from e_usertype))) line

from dual)

select t.usertype_code

from base t, baseLine t1

where t.line = t1.line

执行计划如下

总结一下：

这条语句select usertype_code, rownum line from e_usertype 执行结果如下，共6条数据。把这个结果集当表t

usertype_code  line

01                   1

02                   2

03 3

04 4

05 5

00 6

这条语句select floor(DBMS_RANDOM.VALUE(1, (select count(*) from e_usertype))) line  from dual ，会随机得到一个1到总条数的整数，即1-6间的一个数。把这个结果集当表t1

按道理来讲，两个结果集关联，应该得到一条记录才对，但为什么会有时得不到记录，有时得到多条呢？

我们按nl(t,t1)来进行模拟，要使用t.line 与 t1.line 比较6次，关键在于 t1.line 会每次不同。

也可以使用有固化结果集副作用的其它方法，比如使用 max  min distinct 等，作用也是一样的。

with base as

(select usertype_code, rownum line from e_usertype),

baseLine as --使用max 固化

(select max(floor(DBMS_RANDOM.VALUE(1, (select count(*) from e_usertype)))) line

from dual)

select t.usertype_code

from base t, baseLine t1

where t.line = t1.line

;

with base as

(select usertype_code, rownum line from e_usertype),

baseLine as --使用min 固化

(select min(floor(DBMS_RANDOM.VALUE(1, (select count(*) from e_usertype)))) line

from dual)

select t.usertype_code

from base t, baseLine t1

where t.line = t1.line;

with base as

(select usertype_code, rownum line from e_usertype),

baseLine as --使用min 固化

(select distinct floor(DBMS_RANDOM.VALUE(1, (select count(*) from e_usertype))) line

from dual)

select t.usertype_code

from base t, baseLine t1

where t.line = t1.line

如上几种方法都是正确的。