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條件執行順序是什么就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。