ITPub博客

首页 > 数据库 > Oracle > ORACLE 执行计划会不会影响查询结果

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

原创 Oracle 作者:self_control 时间:2015-09-23 15:46:26 0 删除 编辑
       在读这篇文章时请先问自己两个问题
      
      1、查询语句中,select部分字段的增减会不会影响结果条数?
      2、ORACLE 执行计划会不会影响查询结果?  
    
     下面开始实验
     
       新建一个测试表

点击(此处)折叠或打开

  1. create table E_USERTYPE
  2. (
  3.   USERTYPE_CODE VARCHAR2(50),
  4.   USERTYPE_NAME VARCHAR2(100)
  5. )
        插入几条记录
        

点击(此处)折叠或打开

  1. insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)
  2. values ('01', '第一产业用电');

  3. insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)
  4. values ('02', '第二产业用电');

  5. insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)
  6. values ('03', '第三产业用电');

  7. insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)
  8. values ('04', '居民生活用电');

  9. insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)
  10. values ('05', '工业用电');

  11. insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)
  12. values ('99', '全社会用电');

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

点击(此处)折叠或打开

  1. --为原表加一个行号
  2. with base as (select usertype_code,rownum line from e_usertype ),
  3. --随机出一个1到 e_usertype 总行数数字
  4. baseLine as (select floor(DBMS_RANDOM.VALUE(1,(select count(*) from e_usertype))) line from dual)
  5. --两个虚表进行关联,随机得到一条记录
  6. select t.usertype_code from base t,baseLine t1 where t.line = t1.line
         但执行上面语句是发现问题,它不能实现随机出一条记录,它的结果有时是多条,更多时候是一条都没有。当我改变一个查询语句的字段时,情况发生了变化。
        如下,仅仅是在最后的查询语句中加了一个t1.line 这个字段,结果就正常了,每次执行都可以随机得到一条记录。  

点击(此处)折叠或打开

  1. --为原表加一个行号
  2. with base as (select usertype_code,rownum line from e_usertype ),
  3. --随机出一个1到 e_usertype 总行数的数字
  4. baseLine as (select floor(DBMS_RANDOM.VALUE(1,(select count(*) from e_usertype))) line from dual)
  5. --两个虚表进行关联,随机得到一条记录
  6. select t.usertype_code,t1.line from base t,baseLine t1 where t.line = t1.line
      到此有点崩溃,因为如果有人问我select 字段的增减会不会影响结果条数,我肯定会回答:不会!但这个测试,我真实的看到了增加了一个t1.line 字段,结果就不一样了。
      
      遇到问题,就分析问题吧。脑中马上想到的就是:执行计划不同了!

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

点击(此处)折叠或打开

  1. --为原表加一个行号
  2. with base as (select usertype_code,rownum line from e_usertype ),
  3. --随机出一个1到 e_usertype 总行数的数字
  4. baseLine as (select floor(DBMS_RANDOM.VALUE(1,(select count(*) from e_usertype))) line from dual)
  5. --两个虚表进行关联,随机得到一条记录
  6. select t.usertype_code from base t,baseLine t1 where t.line = t1.line



点击(此处)折叠或打开

  1. --为原表加一个行号
  2. with base as (select usertype_code,rownum line from e_usertype ),
  3. --随机出一个1到 e_usertype 总行数的数字
  4. baseLine as (select floor(DBMS_RANDOM.VALUE(1,(select count(*) from e_usertype))) line from dual)
  5. --两个虚表进行关联,随机得到一条记录
  6. select t.usertype_code,t1.line from base t,baseLine t1 where t.line = t1.line



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

点击(此处)折叠或打开

  1. with base as
  2.  (select usertype_code, rownum line from e_usertype),
  3. baseLine as
  4.  (select /*+ materialize */
  5.    floor(DBMS_RANDOM.VALUE(1, (select count(*) from e_usertype))) line
  6.     from dual)
  7.     
  8. select t.usertype_code
  9.  from base t, baseLine t1
  10.   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

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



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30066956/viewspace-1807313/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-12-16

  • 博文量
    49
  • 访问量
    372620