【Oracle】where條件執行順序(上篇的問題延伸)

PiscesCanon發表於2017-12-12
Oracle
上篇問題連結：http://blog.itpub.net/30174570/viewspace-2148592/
問題：
  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 where to_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/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30174570/viewspace-2148607/，如需轉載，請註明出處，否則將追究法律責任。

相關文章