Duong, August 17, 2017 - 2:14 am UTC
Hi Tom,
Thanks for your respond.
This is business we have so I still have to stick with IN list of thousand to billion records. We have many sub system use the same DB. One export id to text file, other read that text file, get Id list then update in DB.
About the issue. I've test and found a weird result.
If I have smaller 65537 elements in the IN list => result is OK
If I have 65537 elements in the IN list => result is NG, no element is returned.
If I have 65537 + n (n < 65537) => only n first elements are returned.
If I have 65537 + 2*n (n < 65537) => only n first elements are returned.
And so on...
Would you please explain me why this happen?
And what/why is 65537?
Below is my trace file:
PARSING IN CURSOR #10 len=809993 dep=0 uid=85 oct=3 lid=85 tim=1502964407673438 hv=2086074647 ad='112d1cb58' sqlid='69z1h11y5dy8r'
SELECT ID FROM TEST_DEVIDE_LIST
WHERE (TEST_DEVIDE_LIST.ID IN(1,2,3,4,5,6,7,8, ... 999) OR TEST_DEVIDE_LIST.ID IN(1001,..., 1999) OR .... OR (70001,...,79999)
END OF STMT
PARSE #10:c=164533771,e=164535301,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1140626365,tim=1502964407673437
EXEC #10:c=1627,e=1625,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1140626365,tim=1502964407675773
WAIT #10: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964407676649
FETCH #10:c=118,e=118,p=0,cr=14,cu=0,mis=0,r=200,dep=0,og=1,plh=1140626365,tim=1502964407676785
*** 2017-08-17 17:15:32.536
WAIT #10: nam='SQL*Net message from client' ela= 524845160 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932536882
WAIT #10: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932537012
FETCH #10:c=850,e=851,p=0,cr=14,cu=0,mis=0,r=500,dep=0,og=1,plh=1140626365,tim=1502964932537836
WAIT #10: nam='SQL*Net message from client' ela= 46254 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932584136
WAIT #10: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932584247
FETCH #10:c=826,e=825,p=0,cr=14,cu=0,mis=0,r=500,dep=0,og=1,plh=1140626365,tim=1502964932585046
WAIT #10: nam='SQL*Net message from client' ela= 29148 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932614243
WAIT #10: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932614361
FETCH #10:c=831,e=832,p=0,cr=14,cu=0,mis=0,r=500,dep=0,og=1,plh=1140626365,tim=1502964932615149
WAIT #10: nam='SQL*Net message from client' ela= 19900 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932635093
WAIT #10: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932635191
FETCH #10:c=837,e=837,p=0,cr=14,cu=0,mis=0,r=500,dep=0,og=1,plh=1140626365,tim=1502964932636003
WAIT #10: nam='SQL*Net message from client' ela= 25721 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932661771
WAIT #10: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932661872
FETCH #10:c=832,e=832,p=0,cr=14,cu=0,mis=0,r=500,dep=0,og=1,plh=1140626365,tim=1502964932662678
WAIT #10: nam='SQL*Net message from client' ela= 22925 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932685661
WAIT #10: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932685763
FETCH #10:c=828,e=827,p=0,cr=14,cu=0,mis=0,r=500,dep=0,og=1,plh=1140626365,tim=1502964932686564
WAIT #10: nam='SQL*Net message from client' ela= 26191 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932712804
WAIT #10: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932712908
FETCH #10:c=820,e=820,p=0,cr=14,cu=0,mis=0,r=500,dep=0,og=1,plh=1140626365,tim=1502964932713698
WAIT #10: nam='SQL*Net message from client' ela= 26163 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932739907
WAIT #10: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932740006
FETCH #10:c=811,e=810,p=0,cr=14,cu=0,mis=0,r=500,dep=0,og=1,plh=1140626365,tim=1502964932740791
WAIT #10: nam='SQL*Net message from client' ela= 20448 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932761284
WAIT #10: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932761400
FETCH #10:c=804,e=804,p=0,cr=14,cu=0,mis=0,r=500,dep=0,og=1,plh=1140626365,tim=1502964932762177
WAIT #10: nam='SQL*Net message from client' ela= 62808 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932825030
WAIT #10: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932825140
FETCH #10:c=819,e=818,p=0,cr=14,cu=0,mis=0,r=500,dep=0,og=1,plh=1140626365,tim=1502964932825932
WAIT #10: nam='SQL*Net message from client' ela= 23243 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932849223
STAT #10 id=1 cnt=5200 pid=0 pos=1 obj=0 op='INLIST ITERATOR (cr=154 pr=0 pw=0 time=10778 us)'
STAT #10 id=2 cnt=5200 pid=1 pos=1 obj=75809 op='INDEX UNIQUE SCAN ID (cr=154 pr=0 pw=0 time=0 us cost=2170 size=655355 card=131071)'
CLOSE #10:c=21,e=22,dep=0,type=0,tim=1502964932850016
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932850044
WAIT #0: nam='SQL*Net message from client' ela= 3205 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1502964932853263
=====================
August 18, 2017 - 12:42 pm UTC
65537 is close to a power of 2.
It might well be a bug...but as I said:
"It's just a bad bad bad idea - you'll start to encounter all sorts of boundary cases"
Loosk like you've hit your first (of probably many)