经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 数据库/运维 » Oracle » 查看文章
Oracle中谓词推入导致的数据不一致案例
来源：cnblogs　　作者：小海蟹　　时间：2019/9/24 10:48:47　　对本文有异议

现象

下面的语句， 能查到deviceid 为DEV005M0的记录， 但是加上deviceid = 'DEV005M0'这个条件后， 查询语句查不出结果了。
语句如下：

  1. select * from 
  2. (    select deviceid deviceid, port, cvlan, status, funcswitch, 
  3.             decode(funcswitch, 3, pvlan, 1, pvlan) svlan,
  4.             decode(funcswitch, 3, pvlan, 1, 
  5.             lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan
  6.      from   vlanstatus_pre2bak
  7. )
  8. where  funcswitch <> 2 and evlan is null  -- and  deviceid = 'DEV005M0';

当注释掉deviceid = 'DEV005M0'， 查询结果如下：
file

当增加deviceid = 'DEV005M0'，查询结果没有记录：
file

deviceid的数据类型为char(8), vlanstatus_pre2bak的deviceid数据也没有空格等特殊字符， 非常诡异。下面来具体分析。

分析如下

1. 查询语句， 查询没有记录

  1. select * from 
  2. (    select deviceid deviceid, port, cvlan, status, funcswitch, 
  3.             decode(funcswitch, 3, pvlan, 1, pvlan) svlan,
  4.             decode(funcswitch, 3, pvlan, 1, 
  5.             lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan
  6.      from   vlanstatus_pre2bak
  7. )
  8. where  funcswitch <> 2 and evlan is null  and  deviceid = 'DEV005M0' ;

或者：

  1. with tmptab as
  2. (    
  3.     select  deviceid deviceid, port, cvlan, status, funcswitch, 
  4.             decode(funcswitch, 3, pvlan, 1, pvlan) svlan,
  5.             decode(funcswitch, 3, pvlan, 1, 
  6.             lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan
  7.      from   vlanstatus_pre2bak
  8. )
  9. select * from tmptab 
  10. where  funcswitch <> 2 and evlan is null  and  deviceid = 'DEV005M0' ;

查看执行计划：

  1. SQL> set lines 200
  2. SQL> set pages 200
  3. SQL> explain plan for
  4.   2  select * from 
  5.   3  (    select deviceid deviceid, port, cvlan, status, funcswitch, 
  6.   4              decode(funcswitch, 3, pvlan, 1, pvlan) svlan,
  7.   5              decode(funcswitch, 3, pvlan, 1, 
  8.   6              lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan
  9.   7       from   vlanstatus_pre2bak
  10.   8  )
  11.   9  where  funcswitch <> 2 and evlan is null  and  deviceid = 'DEV005M0' ;
  13. 已解释。
  15. SQL> select * from table(dbms_xplan.display);
  17. PLAN_TABLE_OUTPUT
  18. ----------------------------------------------------------------------------------------------------
  19. Plan hash value: 2175325539
  21. ------------------------------------------------------------------------------------------
  22. | Id  | Operation           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
  23. ------------------------------------------------------------------------------------------
  24. |   0 | SELECT STATEMENT    |                    |    70 | 49350 |   692   (1)| 00:00:09 |
  25. |*  1 |  VIEW               |                    |    70 | 49350 |   692   (1)| 00:00:09 |
  26. |   2 |   WINDOW SORT       |                    |    70 |  3430 |   692   (1)| 00:00:09 |
  27. |*  3 |    TABLE ACCESS FULL| VLANSTATUS_PRE2BAK |    70 |  3430 |   691   (1)| 00:00:09 |
  28. ------------------------------------------------------------------------------------------
  30. Predicate Information (identified by operation id):
  31. ---------------------------------------------------
  33.    1 - filter("FUNCSWITCH"<>2 AND "EVLAN" IS NULL)
  34.    3 - filter("DEVICEID"='DEV005M0')
  36. 已选择16行。
  38. SQL>

查看ID为3的谓词过滤（3 - filter("DEVICEID"='DEV005M0')）， 说明先在表VLANSTATUS_PRE2BAK执行了deviceid = 'DEV005M0'操作。ID为1的谓词过滤只有两个（1 - filter("FUNCSWITCH"<>2 AND "EVLAN" IS NULL)），说明这个查询语句先在子查询里面过滤了deviceid = 'DEV005M0'的记录，然后在整个查询视图执行过滤条件FUNCSWITCH<>2 AND EVLAN IS NULL。这个现象就是谓词推入。

2. 使用materialize的hint避免谓词推入

  1. with tmptab as
  2. (    
  3.     select  /*+materialize*/ deviceid deviceid, port, cvlan, status, funcswitch, 
  4.             decode(funcswitch, 3, pvlan, 1, pvlan) svlan,
  5.             decode(funcswitch, 3, pvlan, 1, 
  6.             lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan
  7.      from   vlanstatus_pre2bak
  8. )
  9. select * from tmptab 
  10. where  funcswitch <> 2 and evlan is null  and  deviceid = 'DEV005M0' ;

file

  1. SQL> set lines 200
  2. SQL> set pages 200
  3. SQL> explain plan for 
  4.   2  with tmptab as
  5.   3  (    
  6.   4      select  /*+materialize*/ deviceid deviceid, port, cvlan, status, funcswitch, 
  7.   5              decode(funcswitch, 3, pvlan, 1, pvlan) svlan,
  8.   6              decode(funcswitch, 3, pvlan, 1, 
  9.   7              lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan
  10.   8       from   vlanstatus_pre2bak
  11.   9  )
  12.  10  select * from tmptab 
  13.  11  where  funcswitch <> 2 and evlan is null  and  deviceid = 'DEV005M0' ;
  15. 已解释。
  17. SQL> select * from table(dbms_xplan.display);
  19. PLAN_TABLE_OUTPUT
  20. ----------------------------------------------------------------------------------------------------
  21. Plan hash value: 1444871666
  23. ----------------------------------------------------------------------------------------------------
  24. | Id  | Operation                  | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  25. ----------------------------------------------------------------------------------------------------
  26. |   0 | SELECT STATEMENT           |                             |   313K|   210M|       |  5062   (1)| 00:01:01 |
  27. |   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |       |            |          |
  28. |   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D66CE_DF9DBBFB |       |       |       |            |          |
  29. |   3 |    WINDOW SORT             |                             |   313K|    14M|    20M|  4492   (1)| 00:00:54 |
  30. |   4 |     TABLE ACCESS FULL      | VLANSTATUS_PRE2BAK          |   313K|    14M|       |   690   (1)| 00:00:09 |
  31. |*  5 |   VIEW                     |                             |   313K|   210M|       |   570   (1)| 00:00:07 |
  32. |   6 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D66CE_DF9DBBFB |   313K|    14M|       |   570   (1)| 00:0
  33. ----------------------------------------------------------------------------------------------------
  35. Predicate Information (identified by operation id):
  36. ---------------------------------------------------
  38.    5 - filter("FUNCSWITCH"<>2 AND "EVLAN" IS NULL AND "DEVICEID"='DEV005M0')
  40. 已选择18行。
  42. SQL>

查看ID为5的谓词过滤条件， 三个条件都是发生在视图view上面。

3. 使用trim或者拼接一个空的字符

  1. select * from 
  2. (    select trim(deviceid) deviceid, port, cvlan, status, funcswitch, 
  3.             decode(funcswitch, 3, pvlan, 1, pvlan) svlan,
  4.             decode(funcswitch, 3, pvlan, 1, 
  5.             lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan
  6.      from   vlanstatus_pre2bak
  7. )
  8. where  funcswitch <> 2 and evlan is null and  deviceid = 'DEV005M0' ;

file

查看执行计划：

  1. SQL> set lines 200
  2. SQL> set lines 300
  3. SQL> set pages 200
  4. SQL> explain plan for 
  5.   2  select * from 
  6.   3  (    select trim(deviceid) deviceid, port, cvlan, status, funcswitch, 
  7.   4              decode(funcswitch, 3, pvlan, 1, pvlan) svlan,
  8.   5              decode(funcswitch, 3, pvlan, 1, 
  9.   6              lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan
  10.   7       from   vlanstatus_pre2bak
  11.   8  ) 
  12.   9  where  funcswitch <> 2 and evlan is null and  deviceid = 'DEV005M0';
  14. 已解释。
  16. SQL> select * from table(dbms_xplan.display);
  18. PLAN_TABLE_OUTPUT
  19. ----------------------------------------------------------------------------------------------------
  20. Plan hash value: 2175325539
  22. --------------------------------------------------------------------------------------------------
  23. | Id  | Operation           | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  24. --------------------------------------------------------------------------------------------------
  25. |   0 | SELECT STATEMENT    |                    |   313K|   209M|       |  4492   (1)| 00:00:54 |
  26. |*  1 |  VIEW               |                    |   313K|   209M|       |  4492   (1)| 00:00:54 |
  27. |   2 |   WINDOW SORT       |                    |   313K|    14M|    20M|  4492   (1)| 00:00:54 |
  28. |   3 |    TABLE ACCESS FULL| VLANSTATUS_PRE2BAK |   313K|    14M|       |   690   (1)| 00:00:09 |
  29. --------------------------------------------------------------------------------------------------
  31. Predicate Information (identified by operation id):
  32. ---------------------------------------------------
  34.    1 - filter("FUNCSWITCH"<>2 AND "EVLAN" IS NULL AND "DEVICEID"='DEV005M0')
  36. 已选择15行。
  38. SQL>

查看谓词过滤， 三个过滤条件都发生在ID为1的view上面。

原文链接：http://www.cnblogs.com/xiaohe001/p/11576785.html

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

Oracle热门文章

  • 【Oracle学习笔记】Oralce随机数与随机取样
  • java.sql.SQLSyntaxErrorException: ORA-01722: 无效数字
  • .NET多种数据库大数据批量插入、更新（支持SqlServer、MySql、PgSql和Oracle）
  • Navicat Premium 连接oracle ORA-01017：用户名/口令无效；登陆被拒绝
  • oracle - for in loop 循环更新
  • oracle json 解析函数
  • expdp导出时报错ora-16000
  • WARNING OGG-01519
  • ORA-12514 TNS 监听程序当前无法识别连接描述符中请求服务
  • oracle数据库与其他数据库区别

    • Oracle推荐文章

  • 在RockyLinux 9.3环境中采用RPM模式部署Oracle 19C
  • 你真会判断DataGuard的延迟吗？
  • Oracle rac环境的数据库导入操作记录
  • 使用Oracle进行数据库备份与还原
  • 从零写一个兼容MySQL/Oracle的Proxy中件间（一）《初识Oracle的通信协议》
  • 一文详解TDSQL PG版Oracle兼容性实践
  • 达梦与Oracle逻辑备份与还原性能对比
  • [20211213]提示precompute_subquery.txt
  • ORA-01536: space quota exceeded for tablespace案例
  • ORACLE如何查看当前账号的相关信息总结

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号