溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

Oracle中where條件執(zhí)行順序是什么

發(fā)布時(shí)間：2021-11-12 15:06:35 來源：億速云 閱讀：165 作者：柒染 欄目：關(guān)系型數(shù)據(jù)庫

這篇文章將為大家詳細(xì)講解有關(guān)Oracle中where條件執(zhí)行順序是什么，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對相關(guān)知識(shí)有一定的了解。

問題：

  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;”執(zhí)行成功，換成v$parameter卻報(bào)錯(cuò)。

實(shí)驗(yàn)研究過程：

  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

一開始看到這個(gè)執(zhí)行計(jì)劃很懵逼，完全搞不懂為什么能夠執(zhí)行成功，做10046,10053，改寫sql加hint還是搞不懂。
最后猜想Oracle在filter("NAME"='db_block_size' AND TO_NUMBER("VALUE")=8192)這個(gè)步驟，是先對數(shù)據(jù)做name='db_block_size'的過濾，在做to_number('value')=8192的過濾。
若是能將謂詞信息改變成filter(TO_NUMBER("VALUE") AND  "NAME"='db_block_size'=8192)并且執(zhí)行報(bào)錯(cuò)，那么猜想就是正確的。
嘗試將sql語句的and條件調(diào)換位置"select value from t whereto_number(value)=8192  and  name='db_block_size';",不過還是和原來一樣，這里省略步驟。

這里構(gòu)造其他測試表：

  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

這里執(zhí)行以下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后條件位置互換而已。
查看執(zhí)行結(jié)果：

  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

②和④只是位置不同，但是一個(gè)卻正常執(zhí)行，一個(gè)卻報(bào)錯(cuò)了。
這里查看兩條sql的執(zhí)行計(jì)劃：

  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.

這里對比謂詞信息剛好是兩個(gè)位置不同，導(dǎo)致執(zhí)行結(jié)果不一樣。
正好說明上邊的問題的猜想：
    最后猜想Oracle在filter("NAME"='db_block_size' AND TO_NUMBER("VALUE")=8192)這個(gè)步驟，是先對數(shù)據(jù)做name='db_block_size'的過濾，在做to_number('value')=8192的過濾。
    若是能將謂詞信息改變成filter(TO_NUMBER("VALUE") AND  "NAME"='db_block_size'=8192)并且執(zhí)行報(bào)錯(cuò)，那么猜想就是正確的。

由此問題解決。

其他：

  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后邊條件的執(zhí)行順序，實(shí)際上和執(zhí)行計(jì)劃謂詞信息的順序有關(guān)，和where的位置無關(guān)。
網(wǎng)上有些在10g做實(shí)驗(yàn)得出結(jié)論是從右到左，在11g里邊，按照相同步驟執(zhí)行并得不出相同結(jié)論。

問題延伸：
filter("NAME"='db_block_size' AND TO_NUMBER("VALUE")=8192)，這里是一次性掃描出全部數(shù)據(jù)在進(jìn)行過濾，還是一行一行獲取在判斷的。
延伸鏈接：http://blog.itpub.net/30174570/viewspace-2149212/

關(guān)于Oracle中where條件執(zhí)行順序是什么就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手