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