Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What is Oracle arraysize's research like?

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.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report