Skip to Main Content
  • Questions
  • Querry with big list using IN, result is totally wrong

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: July 23, 2017 - 5:08 am UTC

Last updated: August 18, 2017 - 12:42 pm UTC

Version: 11.2.0.1

Viewed 1000+ times

You Asked

Dear Tom,
I have a table, TEST(ID, DeleteFlg) has around 1 million records. All I need to do is update the DeleteFlg of an given idList(100.000 elements).
What I've tried to do is:
UPDATE TEST
SET DeleteFlg = true
WHERE (ID IN(1,2 ..1000) OR ID IN(1001...2000) OR ... OR ID IN(99001,..99999))
=> Result: only 34463 record updated

OR I tried
SELECT count(1) FROM TEST WHERE (ID IN(1,2 ..1000) OR ID IN(1001...2000) OR ... OR ID IN(99001,..99999))
=> Result just returns only 34463

Would you please explain me why this happen and what is solution for this.

Thanks in advanced,
John

and Connor said...

Wow.... please tell me this is just an exercise and not in any way intended as a true solution for anything for your applications...

There's a reason we have a limit on 1000 items in an IN-list. Because anything more than that means it is NOT the way you should be doing your SQL coding. The parse overheads become enormous, the SQL's become ridiculously long.

It's just a bad bad bad idea - you'll start to encounter all sorts of boundary cases.

eg

SQL> create table t as
  2  select rownum x from dual
  3  connect by level <= 200000;

Table created.

SQL>
SQL> set serverout on
SQL> declare
  2    l_sql clob;
  3    l_parm varchar2(32767);
  4  begin
  5    dbms_lob.createtemporary(l_sql,true);
  6    l_sql := 'delete from t';
  7
  8    l_parm := ' where x in (';
  9    for i in 1 .. 1000 loop
 10      l_parm := l_parm || i ||',';
 11    end loop;
 12    l_parm := rtrim(l_parm,',')||')';
 13    dbms_lob.writeappend(l_sql,length(l_parm),l_parm);
 14
 15    for j in 1 .. 10 loop
 16      l_parm := ' or x in (';
 17      for i in j*1000+1 .. (j+1)*1000 loop
 18        l_parm := l_parm || i ||',';
 19      end loop;
 20      l_parm := rtrim(l_parm,',')||')';
 21      dbms_lob.writeappend(l_sql,length(l_parm),l_parm);
 22    end loop;
 23
 24    dbms_output.put_line('SQL length = '||dbms_lob.getlength(l_sql));
 25
 26    execute immediate l_sql;
 27    dbms_output.put_line(sql%rowcount);
 28  end;
 29  /
SQL length = 55020
11000

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL>   set serverout on
SQL> declare
  2    l_sql clob;
  3    l_parm varchar2(32767);
  4  begin
  5    dbms_lob.createtemporary(l_sql,true);
  6    l_sql := 'delete from t';
  7
  8    l_parm := ' where x in (';
  9    for i in 1 .. 1000 loop
 10      l_parm := l_parm || i ||',';
 11    end loop;
 12    l_parm := rtrim(l_parm,',')||')';
 13    dbms_lob.writeappend(l_sql,length(l_parm),l_parm);
 14
 15    for j in 1 .. 20 loop   -- doubled the number of iterations
 16      l_parm := ' or x in (';
 17      for i in j*1000+1 .. (j+1)*1000 loop
 18        l_parm := l_parm || i ||',';
 19      end loop;
 20      l_parm := rtrim(l_parm,',')||')';
 21      dbms_lob.writeappend(l_sql,length(l_parm),l_parm);
 22    end loop;
 23
 24    dbms_output.put_line('SQL length = '||dbms_lob.getlength(l_sql));
 25
 26    execute immediate l_sql;
 27    dbms_output.put_line(sql%rowcount);
 28  end;
 29  /
ERROR:
ORA-03114: not connected to ORACLE


declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 10840
Session ID: 267 Serial number: 48091


If you have 100,000 items to delete - put them into a table, and then do:

delete from T
where item in ( select id from item_table );

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 24, 2017 - 2:07 am UTC

Thanks for your quick respond Connor :)
Unfortunately, this is in our application came from solution of previous version.

From your example, I can understand that, if the parse overhead oracle db will have error (ORA-03113). Do you have any
documentation about this as I have to show it to my boss.

In the worst case I have to stick with IN() OR IN(), what is the max number of OR (max number elements of list) that I can use safely?

I am just curious, why the result return wrong without and warning or error?

Thanks & Warmest Regards,
John

Connor McDonald
July 25, 2017 - 7:13 am UTC

Dont get me wrong, you should not *see* a ORA-3113 error - that is a bug. Similarly, if you are getting wrong results.

But basically, you're heading into boundary case territory, ie, very few people will be writing queries with tens of thousands of in-list items. So *you* become the customer that finds the bug - not such a great place to be.

You can test out parsing yourself, simply do:

exec dbms_monitor.session_trace_enable;
[run your sql]
exec dbms_monitor.session_trace_disable;

and check the trace file (tkprof formatted).



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
=====================
Connor McDonald
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)

Duong, August 18, 2017 - 6:51 am UTC

Hi Tom,
Sorry I correct the pattern test:
Before:
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?

After:
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 2*65537 + n (n < 65537) => only n first elements are returned.
If I have 3*65537 + 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?

Thanks,
Duong

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.