【Oracle】arraysize的研究（存在疑問）

PiscesCanon發表於2017-12-25
Oracle

  1. SYS@proc> desc aaa;
  2.  Name                                      Null?    Type
  3.  ----------------------------------------- -------- ----------------------------
  4.  ID1                                                NUMBER(38)
  5.  ID2                                                NUMBER(38)
  6.  ID3                                                NUMBER(38)
  7.  ID4                                                NUMBER(38)

  9. SYS@proc> select * from aaa;

  11.        ID1        ID2        ID3        ID4
  12. ---------- ---------- ---------- ----------
  13.          1          1          1          1
  14.          1          1          1          0

  16. SYS@proc> select * from aaa1;

  18.        ID1        ID2        ID3        ID4
  19. ---------- ---------- ---------- ----------
  20.          1          1          1          0
  21.          1          1          1          1

  23. SYS@proc> select * from aaa where id1/id2=1 and id3/id4=1;
  24. ERROR:
  25. ORA-01476: divisor is equal to zero



  29. no rows selected

  31. SYS@proc> set arraysize 1
  32. SYS@proc> /

  34.        ID1        ID2        ID3        ID4
  35. ---------- ---------- ---------- ----------
  36.          1          1          1          1
  37. ERROR:
  38. ORA-01476: divisor is equal to zero



  42. SYS@proc> select * from aaa1 where id1/id2=1 and id3/id4=1;
  43. select * from aaa1 where id1/id2=1 and id3/id4=1
  44.                                           *
  45. ERROR at line 1:
  46. ORA-01476: divisor is equal to zero

  1. SYS@proc> drop table aaa2 purge;

  3. Table dropped.

  5. SYS@proc> create table aaa2 (id1 int,id2 int,id3 int,id4 int,flag int);

  7. Table created.

  9. SYS@proc> insert into aaa2 values(1,1,1,1,1);

  11. 1 row created.

  13. SYS@proc> insert into aaa2 values(1,1,1,1,2);

  15. 1 row created.

  17. SYS@proc> insert into aaa2 values(1,1,1,1,3);

  19. 1 row created.

  21. SYS@proc> insert into aaa2 values(1,1,1,1,4);

  23. 1 row created.

  25. SYS@proc> insert into aaa2 values(1,1,1,1,5);

  27. 1 row created.

  29. SYS@proc> insert into aaa2 values(1,1,1,1,6);

  31. 1 row created.

  33. SYS@proc> insert into aaa2 values(1,1,1,1,7);

  35. 1 row created.

  37. SYS@proc> insert into aaa2 values(1,1,1,1,8);

  39. 1 row created.

  41. SYS@proc> insert into aaa2 values(1,1,1,1,9);

  43. 1 row created.

  45. SYS@proc> insert into aaa2 values(1,1,1,1,10);

  47. 1 row created.

  49. SYS@proc> insert into aaa2 values(1,1,1,0,0);

  51. 1 row created.

  53. SYS@proc> commit;

  55. Commit complete.

  57. SYS@proc> analyze table aaa2 compute statistics;

  59. Table analyzed.

  61. SYS@proc> set arraysize 1
  62. SYS@proc> select * from aaa2 where id1/id2=1 and id3/id4=1;

  64.        ID1        ID2        ID3        ID4       FLAG
  65. ---------- ---------- ---------- ---------- ----------
  66.          1          1          1          1          1
  67.          1          1          1          1          2
  68.          1          1          1          1          3
  69.          1          1          1          1          4
  70.          1          1          1          1          5
  71.          1          1          1          1          6
  72.          1          1          1          1          7
  73.          1          1          1          1          8
  74.          1          1          1          1          9
  75. ERROR:
  76. ORA-01476: divisor is equal to zero



  80. 9 rows selected.
  81. --從大量結果上看，arraysize為1或者2是一樣的。這裡為9預見。

  83. SYS@proc> select * from aaa2;

  85.        ID1        ID2        ID3        ID4       FLAG
  86. ---------- ---------- ---------- ---------- ----------
  87.          1          1          1          1          1
  88.          1          1          1          1          2
  89.          1          1          1          1          3
  90.          1          1          1          1          4
  91.          1          1          1          1          5
  92.          1          1          1          1          6
  93.          1          1          1          1          7
  94.          1          1          1          1          8
  95.          1          1          1          1          9
  96.          1          1          1          1         10
  97.          1          1          1          0          0

  99. 11 rows selected.

  101. SYS@proc>

  1. SYS@proc> set arraysize 2
  2. SYS@proc> select * from aaa2 where id1/id2=1 and id3/id4=1;

  4.        ID1     ID2     ID3    ID4     FLAG
  5. ---------- ---------- ---------- ---------- ----------
  6.      1     1     1     1     1
  7.      1     1     1     1     2
  8.      1     1     1     1     3
  9.      1     1     1     1     4
  10.      1     1     1     1     5
  11.      1     1     1     1     6
  12.      1     1     1     1     7
  13.      1     1     1     1     8
  14. ERROR:
  15. ORA-01476: divisor is equal to zero



  19. 8 rows selected.
語句對應的10046，可以看出是返回了9行，但是從上邊看是8行，很奇怪。
  1. PARSING IN CURSOR #140496887317072 len=48 dep=0 uid=0 oct=3 lid=0 tim=1514130832420098 hv=3007681721 ad='812bd000' sqlid='7cfwyuytnb55t'
  2. select * from aaa2 where id1/id2=1 and id3/id4=1
  3. END OF STMT
  4. PARSE #140496887317072:c=0,e=1221,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2576342259,tim=1514130832420093
  5. EXEC #140496887317072:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2576342259,tim=1514130832420198
  6. WAIT #140496887317072: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832420238
  7. FETCH #140496887317072:c=0,e=64,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=2576342259,tim=1514130832420331
  8. WAIT #140496887317072: nam='SQL*Net message from client' ela= 476 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832420842
  9. WAIT #140496887317072: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832420907
  10. FETCH #140496887317072:c=0,e=36,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=2576342259,tim=1514130832420924
  11. WAIT #140496887317072: nam='SQL*Net message from client' ela= 146 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421092
  12. WAIT #140496887317072: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421137
  13. FETCH #140496887317072:c=0,e=28,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=2576342259,tim=1514130832421151
  14. WAIT #140496887317072: nam='SQL*Net message from client' ela= 66 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421237
  15. WAIT #140496887317072: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421277
  16. FETCH #140496887317072:c=0,e=26,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=2576342259,tim=1514130832421290
  17. WAIT #140496887317072: nam='SQL*Net message from client' ela= 60 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421369
  18. WAIT #140496887317072: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421407
  19. FETCH #140496887317072:c=0,e=25,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=2576342259,tim=1514130832421420
  20. WAIT #140496887317072: nam='SQL*Net message from client' ela= 410 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421848
  21. WAIT #140496887317072: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421907
  22. FETCH #140496887317072:c=0,e=63,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2576342259,tim=1514130832421956
  23. STAT #140496887317072 id=1 cnt=10 pid=0 pos=1 obj=88977 op='TABLE ACCESS FULL AAA2 (cr=7 pr=0 pw=0 time=94 us cost=2 size=10 card=1)'
  24. WAIT #140496887317072: nam='SQL*Net break/reset to client' ela= 28 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=1514130832422110
  25. WAIT #140496887317072: nam='SQL*Net break/reset to client' ela= 120 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=1514130832422252
  26. WAIT #140496887317072: nam='SQL*Net message from client' ela= 595 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832422889
  27. CLOSE #140496887317072:c=0,e=14,dep=0,type=0,tim=1514130832422981
  28. =====================
所以其實arraysize是1還是2，還是存在區別的。不過從10046上看卻是沒多大區別，從全表掃描或者其他能夠正常返回結果的情況下，值為1和2是完全一樣的。


但是實際上無論arraysize的值是多少，預設第一行單獨會直接傳送反饋給使用者的，所以應該是不用設定的。
後邊研究的邏輯讀也有點問題，在12C裡邊差別更大。
其他：http://www.itpub.net/thread-242144-1-1.html

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

相關文章