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

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

问题：

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