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

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

问题：

1. SYS@proc> create table t as select * from v$parameter;
   

2. 
   

3. Table created.
   

4. 
   

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

6. 
   

7. VALUE
   

8. --------------------------------------------------------------------------------
   

9. 8192
   

10. 
    

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;
   

3. 
   

4. Table analyzed.
   

5. 
   

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

7. 
   

8. VALUE
   

9. --------------------------------------------------------------------------------
   

10. 8192
    

11. 
    

12. 
    

13. Execution Plan
    

14. ----------------------------------------------------------
    

15. Plan hash value: 1601196873
    

16. 
    

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. --------------------------------------------------------------------------
    

23. 
    

24. Predicate Information (identified by operation id):
    

25. ---------------------------------------------------
    

26. 
    

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

28. 
    

29. 
    

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);
   

2. 
   

3. Table created.
   

4. 
   

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

6. 
   

7. 1 row created.
   

8. 
   

9. SYS@proc> commit;
   

10. 
    

11. Commit complete.
    

12. 
    

13. SYS@proc> select * from a;
    

14. 
    

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
   

6. 
   

7. 
   

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

9. 
   

10. no rows selected
    

11. 
    

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
    

17. 
    

18. 
    

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;
   

2. 
   

3. Explained.
   

4. 
   

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

6. 
   

7. PLAN_TABLE_OUTPUT
   

8. --------------------------------------------------------------------------------
   

9. Plan hash value: 864433273
   

10. 
    

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. -----------------------------------------------------------------------
    

17. 
    

18. Predicate Information (identified by operation id):
    

19. ---------------------------------------------------
    

20. 
    

21. PLAN_TABLE_OUTPUT
    

22. --------------------------------------------------------------------------------
    

23. 
    

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

25. 
    

26. 13 rows selected.
    

27. 
    

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

29. 
    

30. Explained.
    

31. 
    

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

33. 
    

34. PLAN_TABLE_OUTPUT
    

35. --------------------------------------------------------------------------------
    

36. Plan hash value: 864433273
    

37. 
    

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. ------------------------------------------------------------------------
    

44. 
    

45. Predicate Information (identified by operation id):
    

46. ---------------------------------------------------
    

47. 
    

48. PLAN_TABLE_OUTPUT
    

49. --------------------------------------------------------------------------------
    

50. 
    

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

52. 
    

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);
   

2. 
   

3. Table created.
   

4. 
   

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

6. 
   

7. 1 row created.
   

8. SYS@proc> commit;
   

9. 
   

10. Commit complete.
    

11. 
    

12. SYS@proc> select * from test;
    

13. 
    

14.     ID
    

15. ----------
    

16. 
    

17. 
    

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;
    

20. 
    

21. VALUE
    

22. --------------------------------------------------------------------------------
    

23. 8192
    

24. 
    

25. 
    

26. Execution Plan
    

27. ----------------------------------------------------------
    

28. Plan hash value: 423998170
    

29. 
    

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. ---------------------------------------------------------------------------
    

38. 
    

39. Predicate Information (identified by operation id):
    

40. ---------------------------------------------------
    

41. 
    

42.    3 - filter(TO_CHAR("A"."ID")||"NAME"='db_block_size' AND
    

43.      TO_NUMBER(TO_CHAR("A"."ID")||"T"."VALUE")=8192)
    

44. 
    

45. Note
    

46. -----
    

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

48. 
    

49. 
    

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
    

63. 
    

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
    

70. 
    

71. 
    

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';
    

73. 
    

74. Explained.
    

75. 
    

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

77. 
    

78. PLAN_TABLE_OUTPUT
    

79. --------------------------------------------------------------------------------
    

80. Plan hash value: 423998170
    

81. 
    

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. ---------------------------------------------------------------------------
    

90. 
    

91. 
    

92. PLAN_TABLE_OUTPUT
    

93. --------------------------------------------------------------------------------
    

94. Predicate Information (identified by operation id):
    

95. ---------------------------------------------------
    

96. 
    

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

98.      TO_CHAR("A"."ID")||"NAME"='db_block_size')
    

99. 
    

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