- SYS@proc> desc aaa;
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- ID1 NUMBER(38)
- ID2 NUMBER(38)
- ID3 NUMBER(38)
- ID4 NUMBER(38)
-
- SYS@proc> select * from aaa;
-
- ID1 ID2 ID3 ID4
- ---------- ---------- ---------- ----------
- 1 1 1 1
- 1 1 1 0
-
- SYS@proc> select * from aaa1;
-
- ID1 ID2 ID3 ID4
- ---------- ---------- ---------- ----------
- 1 1 1 0
- 1 1 1 1
-
- SYS@proc> select * from aaa where id1/id2=1 and id3/id4=1;
- ERROR:
- ORA-01476: divisor is equal to zero
-
-
-
- no rows selected
-
- SYS@proc> set arraysize 1
- SYS@proc> /
-
- ID1 ID2 ID3 ID4
- ---------- ---------- ---------- ----------
- 1 1 1 1
- ERROR:
- ORA-01476: divisor is equal to zero
-
-
-
- SYS@proc> select * from aaa1 where id1/id2=1 and id3/id4=1;
- select * from aaa1 where id1/id2=1 and id3/id4=1
- *
- ERROR at line 1:
- ORA-01476: divisor is equal to zero
- SYS@proc> drop table aaa2 purge;
-
- Table dropped.
-
- SYS@proc> create table aaa2 (id1 int,id2 int,id3 int,id4 int,flag int);
-
- Table created.
-
- SYS@proc> insert into aaa2 values(1,1,1,1,1);
-
- 1 row created.
-
- SYS@proc> insert into aaa2 values(1,1,1,1,2);
-
- 1 row created.
-
- SYS@proc> insert into aaa2 values(1,1,1,1,3);
-
- 1 row created.
-
- SYS@proc> insert into aaa2 values(1,1,1,1,4);
-
- 1 row created.
-
- SYS@proc> insert into aaa2 values(1,1,1,1,5);
-
- 1 row created.
-
- SYS@proc> insert into aaa2 values(1,1,1,1,6);
-
- 1 row created.
-
- SYS@proc> insert into aaa2 values(1,1,1,1,7);
-
- 1 row created.
-
- SYS@proc> insert into aaa2 values(1,1,1,1,8);
-
- 1 row created.
-
- SYS@proc> insert into aaa2 values(1,1,1,1,9);
-
- 1 row created.
-
- SYS@proc> insert into aaa2 values(1,1,1,1,10);
-
- 1 row created.
-
- SYS@proc> insert into aaa2 values(1,1,1,0,0);
-
- 1 row created.
-
- SYS@proc> commit;
-
- Commit complete.
-
- SYS@proc> analyze table aaa2 compute statistics;
-
- Table analyzed.
-
- SYS@proc> set arraysize 1
- SYS@proc> select * from aaa2 where id1/id2=1 and id3/id4=1;
-
- ID1 ID2 ID3 ID4 FLAG
- ---------- ---------- ---------- ---------- ----------
- 1 1 1 1 1
- 1 1 1 1 2
- 1 1 1 1 3
- 1 1 1 1 4
- 1 1 1 1 5
- 1 1 1 1 6
- 1 1 1 1 7
- 1 1 1 1 8
- 1 1 1 1 9
- ERROR:
- ORA-01476: divisor is equal to zero
-
-
-
- 9 rows selected.
- --从大量结果上看,arraysize为1或者2是一样的。这里为9预见。
-
- SYS@proc> select * from aaa2;
-
- ID1 ID2 ID3 ID4 FLAG
- ---------- ---------- ---------- ---------- ----------
- 1 1 1 1 1
- 1 1 1 1 2
- 1 1 1 1 3
- 1 1 1 1 4
- 1 1 1 1 5
- 1 1 1 1 6
- 1 1 1 1 7
- 1 1 1 1 8
- 1 1 1 1 9
- 1 1 1 1 10
- 1 1 1 0 0
-
- 11 rows selected.
-
- SYS@proc>
- SYS@proc> set arraysize 2
- SYS@proc> select * from aaa2 where id1/id2=1 and id3/id4=1;
-
- ID1 ID2 ID3 ID4 FLAG
- ---------- ---------- ---------- ---------- ----------
- 1 1 1 1 1
- 1 1 1 1 2
- 1 1 1 1 3
- 1 1 1 1 4
- 1 1 1 1 5
- 1 1 1 1 6
- 1 1 1 1 7
- 1 1 1 1 8
- ERROR:
- ORA-01476: divisor is equal to zero
-
-
-
- 8 rows selected.
- PARSING IN CURSOR #140496887317072 len=48 dep=0 uid=0 oct=3 lid=0 tim=1514130832420098 hv=3007681721 ad='812bd000' sqlid='7cfwyuytnb55t'
- select * from aaa2 where id1/id2=1 and id3/id4=1
- END OF STMT
- PARSE #140496887317072:c=0,e=1221,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2576342259,tim=1514130832420093
- EXEC #140496887317072:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2576342259,tim=1514130832420198
- WAIT #140496887317072: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832420238
- FETCH #140496887317072:c=0,e=64,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=2576342259,tim=1514130832420331
- WAIT #140496887317072: nam='SQL*Net message from client' ela= 476 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832420842
- WAIT #140496887317072: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832420907
- FETCH #140496887317072:c=0,e=36,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=2576342259,tim=1514130832420924
- WAIT #140496887317072: nam='SQL*Net message from client' ela= 146 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421092
- WAIT #140496887317072: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421137
- FETCH #140496887317072:c=0,e=28,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=2576342259,tim=1514130832421151
- WAIT #140496887317072: nam='SQL*Net message from client' ela= 66 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421237
- WAIT #140496887317072: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421277
- FETCH #140496887317072:c=0,e=26,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=2576342259,tim=1514130832421290
- WAIT #140496887317072: nam='SQL*Net message from client' ela= 60 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421369
- WAIT #140496887317072: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421407
- FETCH #140496887317072:c=0,e=25,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=2576342259,tim=1514130832421420
- WAIT #140496887317072: nam='SQL*Net message from client' ela= 410 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421848
- WAIT #140496887317072: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421907
- FETCH #140496887317072:c=0,e=63,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2576342259,tim=1514130832421956
- 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)'
- WAIT #140496887317072: nam='SQL*Net break/reset to client' ela= 28 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=1514130832422110
- WAIT #140496887317072: nam='SQL*Net break/reset to client' ela= 120 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=1514130832422252
- WAIT #140496887317072: nam='SQL*Net message from client' ela= 595 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832422889
- CLOSE #140496887317072:c=0,e=14,dep=0,type=0,tim=1514130832422981
- =====================
但是实际上无论arraysize的值是多少,默认第一行单独会直接发送反馈给用户的,所以应该是不用设置的。
后边研究的逻辑读也有点问题,在12C里边差别更大。
其他: http://www.itpub.net/thread-242144-1-1.html
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30174570/viewspace-2149212/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30174570/viewspace-2149212/