In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
What is the research of Oracle arraysize? I believe that many inexperienced people are at a loss about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.
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 0
SYS@proc > select * from aaa1
ID1 ID2 ID3 ID4
--
1 1 1 0
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
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 row created.
SYS@proc > insert into aaa2 values (1, 1, 1, 1)
1 row created.
SYS@proc > insert into aaa2 values (1, 1, 1, 1 and 3)
1 row created.
SYS@proc > insert into aaa2 values (1, 1, 1, 1 and 4)
1 row created.
SYS@proc > insert into aaa2 values (1pm 1pm 1pm 5)
1 row created.
SYS@proc > insert into aaa2 values (1, 1, 1, 1 and 6)
1 row created.
SYS@proc > insert into aaa2 values (1pm 1pm 1pm 7)
1 row created.
SYS@proc > insert into aaa2 values (1, 1, 1, 1 and 8)
1 row created.
SYS@proc > insert into aaa2 values (1, 1, 1, 1 and 9)
1 row created.
SYS@proc > insert into aaa2 values (1pm 1pm 1pm 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 2
1 1 1 3
1 1 1 4
1 1 1 5
1 1 1 6
1 1 1 7
1 1 1 8
1 1 1 9
ERROR:
ORA-01476: divisor is equal to zero
9 rows selected.
-- from a large number of results, an arraysize of 1 or 2 is the same. Here is 9 foresight.
SYS@proc > select * from aaa2
ID1 ID2 ID3 ID4 FLAG
--
1 1 1
1 1 1 2
1 1 1 3
1 1 1 4
1 1 1 5
1 1 1 6
1 1 1 7
1 1 1 8
1 1 1 9
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 2
1 1 1 3
1 1 1 4
1 1 1 5
1 1 1 6
1 1 1 7
1 1 1 8
ERROR:
ORA-01476: divisor is equal to zero
8 rows selected.
Statement corresponding to 10046, you can see that 9 rows are returned, but from above it looks like 8 rows, which is very strange.
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 # 1404968873170772Phillips 1221 pamphlet pamphlet crinkle cutlery Mitchell 1 pamphlet 0pyralogy depended 0memorialogLichle 2576342259 timeline 1514130832420093
EXEC # 1404968873170772Participitum 24pyrrhen0pyrrhen0pyrrhen0wish0phae0pyrale0pyrale0pyralic0pyralic0pyritorialCutter0pyrrhen0Difficition0Difficient0Difficition0Diffection0Difficult0Difficult0Diffection0Performa1Magi plhagia2576342259 timeline 1514130832420198
WAIT # 140496887317072: nam='SQL*Net message to client' ela= 5 driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1514130832420238
FETCH # 1404968873170772Phantom 64thecontrol paupero, crumbly2, cutlery, mischievity, pantomime, depantile, oglemagne, plaint, 2576342259, timeline, 1514130832420331
WAIT # 140496887317072: nam='SQL*Net message from client' ela= 476 driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1514130832420842
WAIT # 140496887317072: nam='SQL*Net message to client' ela= 3 driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1514130832420907
FETCH # 1404968873170772Phantom 36 pacific0pyrrology cracklle cutlery 0memoir misunderstanding 0memorialricia2depicition0wagedoglemagectionsplhagia2576342259 timeline 1514130832420924
WAIT # 140496887317072: nam='SQL*Net message from client' ela= 146driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1514130832421092
WAIT # 140496887317072: nam='SQL*Net message to client' ela= 2 driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1514130832421137
FETCH # 1404968873170772Phillips 2814130832421151 timeline 1514130832421151
WAIT # 140496887317072: nam='SQL*Net message from client' ela= 66 driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1514130832421237
WAIT # 140496887317072: nam='SQL*Net message to client' ela= 2 driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1514130832421277
FETCH # 1404968873170772Participium 26pyrrhen0pyralogy crunch0memorialcutting0memorialritorial2depairs0gradingoglemagry2576342259 timeline 1514130832421290
WAIT # 140496887317072: nam='SQL*Net message from client' ela= 60 driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1514130832421369
WAIT # 140496887317072: nam='SQL*Net message to client' ela= 1 driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1514130832421407
FETCH # 140496887317072 Freud Centro Magistracy eBay 25, pendant 0pyrrhen1, Crane 1, cuttle0, Mishi 0, r, 2, depths, gentile, gentile, plhagia2576342259, timeline 1514130832421420
WAIT # 140496887317072: nam='SQL*Net message from client' ela= 410 driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1514130832421848
WAIT # 140496887317072: nam='SQL*Net message to client' ela= 2 driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1514130832421907
FETCH # 140496887317072 Freud Centro Magistracy eBay 63Different pair0Reciproci CRON1ZHER cuttle0MIZOMIZOWELER rituallaw depended 0DifferentiaOGTHY 514130832421956
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 p3room0 obj#=-1 tim=1514130832422110
WAIT # 140496887317072: nam='SQL*Net break/reset to client' ela= 120 driver id=1650815232 break?=0 p3400 obj#=-1 tim=1514130832422252
WAIT # 140496887317072: nam='SQL*Net message from client' ela= 595 driver id=1650815232 # bytes=1 p3room0 obj#=-1 tim=1514130832422889
CLOSE # 140496887317072 Freud cymbals 0pr ePixx 14Cool depended 0pr type0Cool timeline 1514130832422981
=
So in fact, there is a difference between whether arraysize is 1 or 2. However, it doesn't make much difference from 10046. From a full table scan or other situation where you can return results normally, a value of 1 and 2 is exactly the same.
But in fact, no matter what the value of arraysize is, the default first line will send feedback directly to the user, so it should not be set.
There are also some problems with the logical reading studied later, and there is a greater difference in 12C.
After reading the above, have you mastered the research method of Oracle arraysize? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 255
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.