宜宾网站建设公司

Oracle中where条件执行顺序是什么

这篇文章将为大家详细讲解有关Oracle中where条件执行顺序是什么，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

创新互联公司专注于中大型企业的网站设计制作、成都网站制作和网站改版、网站营销服务，追求商业策划与数据分析、创意艺术与技术开发的融合，累计客户近千家，服务满意度达97%。帮助广大客户顺利对接上互联网浪潮，准确优选出符合自己需要的互联网运用，我们将一直专注品牌网站建设和互联网程序开发，在前进的路上，与客户一起成长！

问题：

  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 whereto_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/

关于Oracle中where条件执行顺序是什么就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。


当前标题：Oracle中where条件执行顺序是什么
当前链接：http://ybzwz.com/article/gshosj.html

其他资讯