【Oracle】where条件执行顺序(上篇的问题延伸)

最新推荐文章于 2021-09-06 22:03:17 发布
最新推荐文章于 2021-09-06 22:03:17 发布
阅读量194 收藏
点赞数
文章标签： 数据库
上篇问题链接：http://blog.itpub.net/30174570/viewspace-2148592/
问题：
  1. SYS@proc> create table t as select * from v$parameter;

  3. Table created.

  5. SYS@proc> select value from t where name='db_block_size' and to_number(value)=8192;

  7. VALUE
  8. --------------------------------------------------------------------------------
  9. 8192

  11. SYS@proc> select value from v$parameter where name='db_block_size' and to_number(value)=8192;
  12. select value from v$parameter where name='db_block_size' and to_number(value)=8192
  13.                                                              *
  14. ERROR at line 1:
  15. ORA-01722: invalid number
为什么语句“ select value from t where name = 'db_block_size' and to_number ( value ) = 8192 ; ”执行成功，换成v$parameter却报错。
实验研究过程：
  1. SYS@proc> set autotrace on
  2. SYS@proc> analyze table t compute statistics;

  4. Table analyzed.

  6. SYS@proc> select value from t where name='db_block_size' and to_number(value)=8192;

  8. VALUE
  9. --------------------------------------------------------------------------------
  10. 8192


  13. Execution Plan
  14. ----------------------------------------------------------
  15. Plan hash value: 1601196873

  17. --------------------------------------------------------------------------
  18. | Id | Operation         | Name  | Rows | Bytes | Cost (%CPU)| Time     |
  19. --------------------------------------------------------------------------
  20. |  0 | SELECT STATEMENT  |       |    1 |    26 |       4 (0)| 00:00:01 |
  21. |* 1 |  TABLE ACCESS FULL| T     |    1 |    26 |       4 (0)| 00:00:01 |
  22. --------------------------------------------------------------------------

  24. Predicate Information (identified by operation id):
  25. ---------------------------------------------------

  27.    1 - filter("NAME"='db_block_size' AND TO_NUMBER("VALUE")=8192)


  30. Statistics
  31. ----------------------------------------------------------
  32.      1 recursive calls
  33.      0 db block gets
  34.      9 consistent gets
  35.      0 physical reads
  36.      0 redo size
  37.    525 bytes sent via SQL*Net to client
  38.    523 bytes received via SQL*Net from client
  39.      2 SQL*Net roundtrips to/from client
  40.      0 sorts (memory)
  41.      0 sorts (disk)
  42.      1 rows processed
一开始看到这个执行计划很懵逼，完全搞不懂为什么能够执行成功，做10046,10053，改写sql加hint还是搞不懂。
最后猜想Oracle在 filter ( "NAME" = 'db_block_size' AND TO_NUMBER ( "VALUE" ) = 8192 ) 这个步骤，是先对数据做name='db_block_size'的过滤，在做to_number('value')=8192的过滤。
若是能将谓词信息改变成 filter (TO_NUMBER("VALUE") AND "NAME"='db_block_size'= 8192 ) 并且执行报错，那么猜想就是正确的。
尝试将sql语句的and条件调换位置" select value from t where to_number ( value )=8192 and name='db_block_size' ; ",不过还是和原来一样，这里省略步骤。

这里构造其他测试表：
  1. SYS@proc> create table a(id1 int,id2 int,id3 int,id4 int);

  3. Table created.

  5. SYS@proc> insert into a values(1,1,1,0);

  7. 1 row created.

  9. SYS@proc> commit;

  11. Commit complete.

  13. SYS@proc> select * from a;

  15.        ID1        ID2        ID3        ID4
  16. ---------- ---------- ---------- ----------
  17.          1          1          1          0
这里执行以下4条sql语句：
①Select 'ok' From aaa where id1/id2=1 and id3/id4=2;
②Select 'ok' From aaa where id1/id2=2 and id3/id4=2;
③Select 'ok' From aaa where id3/id4=2 and id1/id2=1;
④Select 'ok' From aaa where id3/id4=2 and id1/id2=2;
其中①和③，②和④只是where后条件位置互换而已。
查看执行结果：
  1. SYS@proc> Select 'ok' From aaa where id1/id2=1 and id3/id4=2;
  2. Select 'ok' From aaa where id1/id2=1 and id3/id4=2
  3.                                             *
  4. ERROR at line 1:
  5. ORA-01476: divisor is equal to zero


  8. SYS@proc> Select 'ok' From aaa where id1/id2=2 and id3/id4=2;

  10. no rows selected

  12. SYS@proc> Select 'ok' From aaa where id3/id4=2 and id1/id2=1;
  13. Select 'ok' From aaa where id3/id4=2 and id1/id2=1
  14.                               *
  15. ERROR at line 1:
  16. ORA-01476: divisor is equal to zero


  19. SYS@proc> Select 'ok' From aaa where id3/id4=2 and id1/id2=2;
  20. Select 'ok' From aaa where id3/id4=2 and id1/id2=2
  21.                               *
  22. ERROR at line 1:
  23. ORA-01476: divisor is equal to zero
②和④只是位置不同，但是一个却正常执行，一个却报错了。
这里查看两条sql的执行计划：
  1. SYS@proc> explain plan for Select 'ok' From aaa where id1/id2=2 and id3/id4=2;

  3. Explained.

  5. SYS@proc> select * from table(dbms_xplan.display());

  7. PLAN_TABLE_OUTPUT
  8. --------------------------------------------------------------------------------
  9. Plan hash value: 864433273

  11. -----------------------------------------------------------------------
  12. | Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
  13. ------------------------------------------------------------------------
  14. | 0  | SELECT STATEMENT  |      |    1 |    12 |     2   (0)| 00:00:01 |
  15. |* 1 |  TABLE ACCESS FULL| AAA  |    1 |    12 |     2   (0)| 00:00:01 |
  16. -----------------------------------------------------------------------

  18. Predicate Information (identified by operation id):
  19. ---------------------------------------------------

  21. PLAN_TABLE_OUTPUT
  22. --------------------------------------------------------------------------------

  24.    1 - filter("ID1"/"ID2"=2 AND "ID3"/"ID4"=2)

  26. 13 rows selected.

  28. SYS@proc> explain plan for Select 'ok' From aaa where id3/id4=2 and id1/id2=2;

  30. Explained.

  32. SYS@proc> select * from table(dbms_xplan.display());

  34. PLAN_TABLE_OUTPUT
  35. --------------------------------------------------------------------------------
  36. Plan hash value: 864433273

  38. ------------------------------------------------------------------------
  39. | Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
  40. ------------------------------------------------------------------------
  41. |  0 | SELECT STATEMENT  |      |    1 |    12 |     2   (0)| 00:00:01 |
  42. |* 1 |  TABLE ACCESS FULL|  AAA |    1 |    12 |     2   (0)| 00:00:01 |
  43. ------------------------------------------------------------------------

  45. Predicate Information (identified by operation id):
  46. ---------------------------------------------------

  48. PLAN_TABLE_OUTPUT
  49. --------------------------------------------------------------------------------

  51.    1 - filter("ID3"/"ID4"=2 AND "ID1"/"ID2"=2)

  53. 13 rows selected.
这里对比谓词信息刚好是两个位置不同，导致执行结果不一样。
正好说明上边的问题的猜想：
     最后猜想Oracle在 filter ( "NAME" = 'db_block_size' AND TO_NUMBER ( "VALUE" ) = 8192 ) 这个步骤，是先对数据做name='db_block_size'的过滤，在做to_number('value')=8192的过滤。
     若是能将谓词信息改变成 filter (TO_NUMBER("VALUE") AND "NAME"='db_block_size'= 8192 ) 并且执行报错，那么猜想就是正确的。
由此问题解决。

其他：
  1. SYS@proc> create table test (id int);

  3. Table created.

  5. SYS@proc> insert into test values(null);

  7. 1 row created.
  8. SYS@proc> commit;

  10. Commit complete.

  12. SYS@proc> select * from test;

  14.     ID
  15. ----------


  18. SYS@proc> set autotrace on
  19. SYS@proc> select value from t,test a where a.id||name='db_block_size' and to_number(a.id||t.value)=8192;

  21. VALUE
  22. --------------------------------------------------------------------------------
  23. 8192


  26. Execution Plan
  27. ----------------------------------------------------------
  28. Plan hash value: 423998170

  30. ---------------------------------------------------------------------------
  31. | Id | Operation          | Name  | Rows | Bytes | Cost (%CPU)| Time     |
  32. ---------------------------------------------------------------------------
  33. |  0 | SELECT STATEMENT   |       |    1 |    39 |    6    (0)| 00:00:01 |
  34. |  1 |  NESTED LOOPS      |       |    1 |    39 |    6    (0)| 00:00:01 |
  35. |  2 |   TABLE ACCESS FULL| TEST  |    1 |    13 |    2    (0)| 00:00:01 |
  36. |* 3 |   TABLE ACCESS FULL| T     |    1 |    26 |    4    (0)| 00:00:01 |
  37. ---------------------------------------------------------------------------

  39. Predicate Information (identified by operation id):
  40. ---------------------------------------------------

  42.    3 - filter(TO_CHAR("A"."ID")||"NAME"='db_block_size' AND
  43.      TO_NUMBER(TO_CHAR("A"."ID")||"T"."VALUE")=8192)

  45. Note
  46. -----
  47.    - dynamic sampling used for this statement (level=2)


  50. Statistics
  51. ----------------------------------------------------------
  52.      32 recursive calls
  53.      0 db block gets
  54.      28 consistent gets
  55.      0 physical reads
  56.      0 redo size
  57.     525 bytes sent via SQL*Net to client
  58.     523 bytes received via SQL*Net from client
  59.      2 SQL*Net roundtrips to/from client
  60.      4 sorts (memory)
  61.      0 sorts (disk)
  62.      1 rows processed

  64. SYS@proc> set autotrace off
  65. SYS@proc> select value from t,test a where to_number(a.id||t.value)=8192 and a.id||name='db_block_size';
  66. select value from t,test a where to_number(a.id||t.value)=8192 and a.id||name='db_block_size'
  67.                                                 *
  68. ERROR at line 1:
  69. ORA-01722: invalid number


  72. SYS@proc> explain plan for select value from t,test a where to_number(a.id||t.value)=8192 and a.id||name='db_block_size';

  74. Explained.

  76. SYS@proc> select * from table(dbms_xplan.display());

  78. PLAN_TABLE_OUTPUT
  79. --------------------------------------------------------------------------------
  80. Plan hash value: 423998170

  82. ---------------------------------------------------------------------------
  83. | Id | Operation          | Name  | Rows | Bytes | Cost (%CPU)| Time     |
  84. ---------------------------------------------------------------------------
  85. |  0 | SELECT STATEMENT   |       |    1 |    39 |    6    (0)| 00:00:01 |
  86. |  1 |  NESTED LOOPS      |       |    1 |    39 |    6    (0)| 00:00:01 |
  87. |  2 |   TABLE ACCESS FULL| TEST   |    1 |    13 |    2    (0)| 00:00:01 |
  88. |* 3 |   TABLE ACCESS FULL| T     |    1 |    26 |    4    (0)| 00:00:01 |
  89. ---------------------------------------------------------------------------


  92. PLAN_TABLE_OUTPUT
  93. --------------------------------------------------------------------------------
  94. Predicate Information (identified by operation id):
  95. ---------------------------------------------------

  97.    3 - filter(TO_NUMBER(TO_CHAR("A"."ID")||"T"."VALUE")=8192 AND
  98.      TO_CHAR("A"."ID")||"NAME"='db_block_size')

  100. 16 rows selected.

所以where后边条件的执行顺序，实际上和执行计划谓词信息的顺序有关，和where的位置无关。
网上有些在10g做实验得出结论是从右到左，在11g里边，按照相同步骤执行并得不出相同结论。

问题延伸：
filter ( "NAME" = 'db_block_size' AND TO_NUMBER ( "VALUE" ) = 8192 ) ，这里是一次性扫描出全部数据在进行过滤，还是一行一行获取在判断的。
延伸链接： http://blog.itpub.net/30174570/viewspace-2149212/

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

转载于:http://blog.itpub.net/30174570/viewspace-2148607/

cong0516
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
ORACLE中where条件的相互影响
m0_37419027的博客
07-29 490
在一次查询过程中，我的where条件中包含了and和or两个关键字，第一次的执行过程，只有两个条件。代码如下： select e.c_time,e.status,A.c_valid from A left join d on A.main_id = d.crcl_id left join e on e.id = d.ious_id where e.status != '1' or (e.status = '1' and to_char(e.c_date,'YYYYMMDD')>to_...
oracle复合索引列顺序,复合索引顺序选择性问题（一）
weixin_29759387的博客
04-04 3602
索引是我们经常选择的数据表检索优化方案之一。其中，复合索引是我们经常选择的策略。那么，构建索引列的顺序上，有何种差异和需要注意的方面呢？下面我们通过实验来进行说明。实验环境说明准备数据表和实验环境。索引列的差异，主要体现在选择性上，我们通过构建不同选择性的列来进行试验。SQL> conn scott/tiger@orcl;Connected to Oracle DatabaseEnterpr...
oracle中 rownum 对排序的影响
南极光经验分享
04-11 423
rownum 很早就会用，以前也经常用在分页查询，但今天发现了一个新问题： 1. 标准的rownum分页查询使用方法： select *  from (select c.*, rownum rn from content c) where rn >= 1   and rn <= 5   2. 但是如果, 加上order by addtime 排序则数据显示不正确 select *  f...
关于Oracle查询时where条件执行顺序
m0_37824791的博客
10-12 2507
因在工作中数据库查询数量百万级表中需要多条件查询，但是筛选条件只有一列有索引，查询效率很低，后查阅资料后修改where后执行的条件顺序，瞬间执行查询语句提速至毫秒级。故以此为记。 eg：SELECT 列名 FROM 表名 WHERE 条件A AND 条件B; 如上很简单的一条查询，在数量少的情况下，where后条件顺序不管如何几乎查询效率都不低，但在百万级数据中检索再加上条件B查询的列上没有创...
MySQL和Oracle中where条件语句的执行顺序
qq_16268979的博客
03-03 4764
面试的时候你可能遇到过面试官提出：SQL的书写顺序，SQL的执行（解析）顺序，WHERE条件执行顺序等问题。这篇文章就简要总结下上面的几个问题。 一、MySQL中SQL的书写顺序和执行顺序 sql编写顺序： select distinct…from…join…on…where…group by…having…order by…limit… sql解析顺序： from…on…join…where…group by…having…select distinct…order by…limit… 二、MySQL和O
oracle select执行顺序的详解
09-10
Oracle SQL的SELECT语句...- **WHERE子句的连接顺序**：在WHERE子句中，应先执行过滤效果最明显的条件，以尽早减少数据量。 理解并应用这些原则，能帮助我们写出更高效、更快的Oracle SQL查询，从而提升数据库的性能。
where条件顺序不同、性能不同示例探讨
09-10
这在很大程度上降低了因where条件顺序不同而产生的性能差异问题。 但即使在优化器变得更加智能的Oracle 11G中，编写查询时还是推荐遵循一些最佳实践，以确保查询的高效性： 1. 避免全表扫描：确保总是利用索引，...
Oracle DML触发器的执行顺序.pdf
10-10
Oracle DML 触发器的执行顺序 Oracle 中的 DML 触发器是一种存储在数据库中的 PL/SQL 块，能够自动响应特定事件，以实现一些复杂的业务规则。DML 触发器可以分为三种基本类型：数据操纵语句 (DML) 触发器、数据定义...
oracle触发器执行顺序.pdf
10-08
oracle触发器执行顺序.pdf
oracle 去掉 filter,oracle全文索引之FILTER_1_NULL_FILTER
weixin_31755877的博客
04-08 308
介绍完Oracle全文索引的DATASTORE属性，继续介绍Oracle的FILTER属性。Oracle全文索引的FILTER属性主要是针对具有一定格式的文档，Oracle根据FILTER的设定来过滤那些不属于文章内容的部分。FILTER属性包含的属性有：CHARSET_FILTER、INSO_FILTER、NULL_FILTER、USER_FILTER、PROCEDURE_FILTER几种。[@...
Oracle数据库中的Where条件执行顺序是从后往前的?
weixin_30711917的博客
03-04 511
先看下面的一小段代码 ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾. 例如: 　　(低效) 　　 SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAG...
Oracle数据库 where语句的执行顺序
xianrenrmx的博客
12-22 2337
Oracle数据库，sql语句，where的执行顺序，是从左到右执行； 例子： 数据库中存储 scarleimp表 |sequence_no|transdate |seller_code| |202012211111|20201126|009| |202012211112|20201125|009| | | | scarle表 |sequence_no|transdate |seller_code| |202012211111|20201126|009| |202012211112|20201125
Oracle WHERE条件执行顺序
风斗士
04-18 2071
sql优化比较复杂,且受环境限制,但开发过程中,写sql要遵循最起码的原则.如下:   1.ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.例如:(低效)SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANA
oracle学习笔记之where条件过滤
weixin_42108533的博客
09-06 483
1.显示员工薪水在1000~2000的员工 方法1 方法1 SQL> select ename 员工，sal 薪水,deptno 部门编号 2 from emp 3 where sal>=1000 and sal<=2000; 员工 薪水 部门编号 -------------------- ---------- ---------- ALLEN 1600
ORACLE中where部分条件执行顺序测试
DBA_JohnHuang
12-10 8959
ORACLE中where部分条件执行顺序测试 实验1：证明oracle SQL的语法分析应该是从右到左的 实验2：证明了oracle SQL条件的执行是从右到左的 实验3：证明同一个where子句中条件的先后顺序对效率影响不大
oracle中where执行顺序,Oracle中where子句中条件的物理执行顺序
weixin_36443241的博客
04-03 576
经常有人问到oracle中的Where子句的条件书写顺序是否对SQL性能有影响，我的直觉是没有影响，因为如果这个顺序有影响，Oracle应该早就能够做到自动优化，但一直没有关于这方面的确凿证据。在网上查到的文章，一般认为在RBO优化器模式下无影响(10G开始，缺省为RBO优化器模式)，而在CBO优化器模式下有影响，主要有两种观点：a.能使结果最少的条件放在最右边，SQL执行是按从右到左进行结果集的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值