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

       在读这篇文章时请先问自己两个问题
      
      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. 
   
4. insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)
   
5. values ('02', '第二产业用电');
   
6. 
   
7. insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)
   
8. values ('03', '第三产业用电');
   
9. 
   
10. insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)
    
11. values ('04', '居民生活用电');
    
12. 
    
13. insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)
    
14. values ('05', '工业用电');
    
15. 
    
16. insert into E_USERTYPE (USERTYPE_CODE, USERTYPE_NAME)
    
17. 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30066956/viewspace-1807313/