Oracle Tips and Tricks — David Fitzjarrell

April 17, 2019

Threadbare

Filed under: General — dfitzjarrell @ 07:51

"Expect everything, I always say, and the unexpected never happens."
-- Norton Juster, The Phantom Tollbooth

Oracle has, since version 11.2, required standby redo logs to be configured for a Data Guard installation, even though in 11.2 the default log apply process relied on archived redo logs, not the standby logs. That changed in 12.1 and the default became what Oracle labelled “Real Time Apply”; the choice to use archived redo logs instead was, and is still available. In 12.2 and later releases another change has been made, this time with respect to creating those standby logs. Let’s look at how that aspect has progressed and what it means in a modern Data Guard configuration.

In days of old (11.2 and 12.1) creating standby logs was as easy as:


SQL> alter database add standby logfile ('/u04/redolog/sbylog1.rdo'.'/u05/redo/sbylog2.rdo') size 500M;

Database altered.

SQL>

and the standby logs would be created and use, by default, thread #1. If a different thread number was required:


SQL> alter database add standby logfile thread 3 ('/u04/redolog/sbylog1.rdo'.'/u05/redo/sbylog2.rdo') size 500M;

Database altered.

SQL>

Since Data Guard starts with thread #1 the default behavior was usually sufficient to create usable standby redo logs. However, in 12.2 that behavior has changed and now the default thread is 0, a thread that Data Guard cannot use. Thus, in 12.2 and later releases the thread must be specified, even if it’s thread 1:


SQL> alter database add standby logfile thread 1 ('/u04/redolog/sbylog1.rdo','/u05/redo/sbylog2.rdo') size 500M;

Database altered.

SQL>

Relying on previous default behavior will result in Data Guard not using the standby logfiles, and using the broker to configure and manage the Data Guard configuration will result in dgmgrl reporting the following warning:


 Database Warning(s): ORA-16789: standby redo logs configured incorrectly

and subsequent monitoring of those standby logs will show they are not being used because they are associated with thread 0:


DBID		  THREAD#  SEQUENCE#	   USED ARCHIVED  STATUS			 FIRST_CHANGE# FIRST_TIME      LAST_CHANGE# LAST_TIME
-------------- ---------- ---------- ---------- --------- ------------------------------ ------------- --------------- ------------ ---------------
UNASSIGNED		0	   0	      0 YES	  UNASSIGNED
UNASSIGNED		0	   0	      0 YES	  UNASSIGNED
UNASSIGNED		0	   0	      0 YES	  UNASSIGNED
UNASSIGNED		0	   0	      0 YES	  UNASSIGNED

Of course managed recovery can be started using archived redo logs in 12.2:


SQL> alter database recover managed standby database using archived logfile disconnect from session;

Database altered.

SQL>

Unless there is a need for a delay in log application (to provide a ‘cushion’ against errant updates to the standby) it’s best to use the standby logfiles. For smaller databases this issue is fairly easy to correct by dropping the original standby logs and creating new ones, specifying the thread. The procedure to do this is as follows:


--
-- Query v$standby_log to find the unused logs
--

SQL> select group#, thread#, status, bytes/1024/1024 from v$standby_log ;

    GROUP#    THREAD# STATUS	 BYTES/1024/1024
---------- ---------- ---------- ---------------
	 4	    0 UNASSIGNED	     200
	 5	    0 UNASSIGNED	     200
	 6	    0 UNASSIGNED	     200
	 7	    0 UNASSIGNED	     200


--
-- Stop standby recovery
--

SQL> alter database recover managed standby database cancel;

Database altered.

--
-- Create new standby logs specifying the correct thread
--
SQL> alter database add standby logfile thread 1 '/u04/redolog/sbylog1.rdo' size 500M;

Database altered.

SQL> alter database add standby logfile thread 1 '/u04/redolog/sbylog2.rdo' size 500M;

Database altered.

SQL> alter database add standby logfile thread 1 '/u04/redolog/sbylog3.rdo' size 500M;

Database altered.

SQL> alter database add standby logfile thread 1 '/u04/redolog/sbylog4.rdo' size 500M;

Database altered.

--
-- Drop the old standby logfiles
-- In this installation those are groups
-- 4, 5, 6, and 7
--
SQL> alter database drop standby logfile group 4;

Database altered.

SQL> alter database drop standby logfile group 5;

Database altered.

SQL> alter database drop standby logfile group 6;

Database altered.

SQL> alter database drop standby logfile group 7;

Database altered.

--
-- Start managed recovery 
--

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Since none of the standby redo logs were in use it was a simple task to drop them without a log switch. Once managed recovery has been started the database should catch up and one of the standby logs should show as being assigned. It’s a simple task to check the progress of the standby logs:


SQL> select dbid, thread#, sequence#, used, archived, status, first_change#, first_time, last_change#, last_time
  2  from v$standby_log;

DBID		  THREAD#  SEQUENCE#	   USED ARC STATUS     FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME
-------------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ ---------
3503443853		1	 446	1934848 YES ACTIVE	  4.5893E+12 15-APR-19	 4.5893E+12 15-APR-19
UNASSIGNED		1	   0	      0 NO  UNASSIGNED
UNASSIGNED		1	   0	      0 YES UNASSIGNED
UNASSIGNED		1	   0	      0 YES UNASSIGNED

And dgmgrl should report no warnings:


DGMGRL> show configuration

Configuration - my_splord_config

  Protection Mode: MaxPerformance
  Members:
  splord          - Primary database
    splord_x86_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 28 seconds ago)

DGMGRL>

Default behavior can change between database releases so it’s a good idea to check the documentation before making any assumptions based on experience with prior database releases; surprises can lurk around any corner.

Especially when they are unexpected.

April 9, 2019

Making A List

Filed under: General — dfitzjarrell @ 15:18

"Expect everything, I always say, and the unexpected never happens."
-- Norton Juster, The Phantom Tollbooth

In-lists can be a convenient way to search for multiple values in a single select statement. They can be hard-coded or can operate on the results of a subquery, and in the first case the reported limitation is 1000 elements, and that is correct for single-valued elements. If the elements are multi-valued that limit changes, which may be a surprise to many. Let’s take that for a spin by creating a test table and populating it:


BLEEBO @ splang > 
BLEEBO @ splang > --
BLEEBO @ splang > -- Create a table for testing
BLEEBO @ splang > --
BLEEBO @ splang > 
BLEEBO @ splang > create table inlist_test(
  2  inval1  number,
  3  inval2  number,
  4  val2return      varchar2(40));

Table created.

BLEEBO @ splang > 
BLEEBO @ splang > --
BLEEBO @ splang > -- Populate the table
BLEEBO @ splang > --
BLEEBO @ splang > 
BLEEBO @ splang > begin
  2  	     for i in 1..3000 loop
  3  		     insert into inlist_test(inval1, inval2, val2return)
  4  		     values(i, i, 'Value '||i);
  5  	     end loop;
  6  
  7  	     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

A query with a hard-coded in-list of 1001 elements fails to execute, throwing the expected error:


BLEEBO @ splang > 
BLEEBO @ splang > --
BLEEBO @ splang > -- IN-LIST with 1001 single elements
BLEEBO @ splang > --
BLEEBO @ splang > 
BLEEBO @ splang > select val2return
  2  from inlist_test
  3  where inval1 in (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
where inval1 in (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 *
ERROR at line 3:
ORA-01795: maximum number of expressions in a list is 1000

Oracle found the extra element and threw the expected error. So far the listed limitation is found to be correcti for single-valued, hard-coded elements. What if, for instance, we use multi-valued elements — does that behavior change? It certainly does. Let’s prove that by using element pairs and make the in-list 2500 members long:


BLEEBO @ splang > 
BLEEBO @ splang > --
BLEEBO @ splang > -- Now try an IN-LIST of pairs
BLEEBO @ splang > --
BLEEBO @ splang > 
BLEEBO @ splang > select val2return
  2  from inlist_test
  3  where (inval1,inval2) in ((1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
  4  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
  5  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
  6  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
  7  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
  8  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
  9  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
 10  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
 11  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1));

VAL2RETURN
----------------------------------------
Value 1
Value 2
Value 3
Value 4
Value 5
Value 6
Value 7
Value 8
Value 9

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3899671621

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     9 |   432 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| INLIST_TEST |     9 |   432 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("INVAL1"=1 AND "INVAL2"=1 OR "INVAL1"=2 AND "INVAL2"=2 OR
              "INVAL1"=3 AND "INVAL2"=3 OR "INVAL1"=4 AND "INVAL2"=4 OR "INVAL1"=5 AND
              "INVAL2"=5 OR "INVAL1"=6 AND "INVAL2"=6 OR "INVAL1"=7 AND "INVAL2"=7 OR
              "INVAL1"=8 AND "INVAL2"=8 OR "INVAL1"=9 AND "INVAL2"=9)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         11  recursive calls
          0  db block gets
         43  consistent gets
          0  physical reads
          0  redo size
        699  bytes sent via SQL*Net to client
       6338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

The expected limitation no longer applies and this list of 2500 elements throws no errors and returns results.

Some might be tempted to cry ‘BUG!!!’ when seeing this given the documented physical limit on in-list length, and a 10053 event trace (which provides the optimizer decision ‘tree’ used to create the execution plan) returns information for all but the first query in the example, which errored at the parse phase, as a portion of a 10046 event trace (execution, wait and bind information) proves:


PARSE ERROR #140283620983280:len=2054 dep=0 uid=102 oct=3 lid=102 tim=26169356395 err=1795

Apparently when in-list elements are composed of pairs, triples, etc. the code that rejects lists longer than 1000 elements is somehow bypassed. Hard-coded or generated by a subquery any in-list composed of multi-valued elements passes through unscathed:


BLEEBO @ splang > 
BLEEBO @ splang > --
BLEEBO @ splang > -- Now let's try a subquery
BLEEBO @ splang > --
BLEEBO @ splang > 
BLEEBO @ splang > select val2return
  2  from inlist_test
  3  where (inval1, inval2) in (select inval1, inval2 from inlist_test);

VAL2RETURN
----------------------------------------
Value 330
Value 331
Value 332
Value 333
Value 334
Value 335
Value 336
Value 337
Value 338
Value 339
Value 340
...
Value 2466
Value 2467
Value 2468
Value 2469
Value 2470
Value 2471
Value 2472
Value 2473

3000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2957450017

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    74 |    10   (0)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|             |     1 |    74 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | INLIST_TEST |  3000 | 78000 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | INLIST_TEST |  3000 |   140K|     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("INVAL1"="INVAL1" AND "INVAL2"="INVAL2")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        265  consistent gets
          0  physical reads
          0  redo size
      74145  bytes sent via SQL*Net to client
       2741  bytes received via SQL*Net from client
        201  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       3000  rows processed

To make this even more interesting a subquery of single-valued elements also successfully executes with more than 1000 members:


BLEEBO @ splang > 
BLEEBO @ splang > --
BLEEBO @ splang > -- There will be 3000 single values from the subquery
BLEEBO @ splang > --
BLEEBO @ splang > 
BLEEBO @ splang > select val2return
  2  from inlist_test
  3  where inval2 in (select inval2 from inlist_test);

VAL2RETURN
----------------------------------------
Value 330
Value 331
Value 332
Value 333
Value 334
Value 335
Value 336
Value 337
Value 338
Value 339
Value 340
...
Value 2470
Value 2471
Value 2472
Value 2473

3000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2957450017

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  3000 |   140K|    10   (0)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|             |  3000 |   140K|    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | INLIST_TEST |  3000 | 39000 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | INLIST_TEST |  3000 |   102K|     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("INVAL2"="INVAL2")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        265  consistent gets
          0  physical reads
          0  redo size
      74145  bytes sent via SQL*Net to client
       2741  bytes received via SQL*Net from client
        201  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3000  rows processed

BLEEBO @ splang > 

But, what if the driving table for the in-list has only one column? Simply manufacture element pairs by including a constant in the element:


BLEEBO @ splang > 
BLEEBO @ splang > --
BLEEBO @ splang > -- If there is only one column for an in-list a
BLEEBO @ splang > -- second column can be 'manufactured' to get
BLEEBO @ splang > -- past the 1000 element limitation
BLEEBO @ splang > --
BLEEBO @ splang > 
BLEEBO @ splang > select val2return
  2  from inlist_test
  3  where (inval2,9) in
  4  ((inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
  5  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
  6  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
  7  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
  8  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
  9  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 10  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 11  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 12  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 13  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 14  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 15  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 16  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 17  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 18  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 19  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 20  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 21  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 22  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 23  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 24  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 25  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 26  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 27  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9));

VAL2RETURN
----------------------------------------
Value 1
Value 2
Value 3
Value 4
Value 5
Value 6
Value 7
Value 8
Value 9
Value 10
Value 11
...
Value 2996
Value 2997
Value 2998
Value 2999
Value 3000

3000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3899671621

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |  3000 |   102K|     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| INLIST_TEST |  3000 |   102K|     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("INVAL2" IS NOT NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        233  consistent gets
          0  physical reads
          0  redo size
      74145  bytes sent via SQL*Net to client
      28041  bytes received via SQL*Net from client
        201  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3000  rows processed

BLEEBO @ splang > 

So Oracle’s ‘hard-and-fast’ limit of 1000 in-list elements … isn’t, depending on how the list is constructed. For hard-coded lists using element pairs gets around the limitation, and subqueries of any construction also make an ‘end run’ around that barrier. Presumably from this demonstration new ‘rules’ regarding in-lists could be created:


1. In-lists can be longer than 1000 members when multi-valued elements are used.
2. Single-valued subqueries can also allow in-lists of more than 1000 members.
3. Multi-valued elements can include constants.

Bug? Possibly, but if this truly is a bug then it’s probably a good idea to not rely on the current behavior as it could be fixed in a future release. As of now, though, it is possible to code in-lists that exceed the 1000-member mark by using the techniques provided here. Don’t be surprised if, one day, such queries fail since this is not documented behavior.

And that shouldn’t be unexpected.

April 5, 2019

Recovery Room

Filed under: recovery — dfitzjarrell @ 14:45

"There are no wrong roads to anywhere."
-- Norton Juster, The Phantom Tollbooth

The common wisdom is that any operation declared NOLOGGING won’t contribute to the redo stream, and that includes operations on tables created NOLOGGING. [It cannot be stressed enough that NOLOGGING operations in production databases should NOT be executed if for no other reason than recoverability.] Yet there are operations that will defy the NOLOGGING directive, generating the usual amount of redo. Insert /*+ append */ operations obey NOLOGGING, as do truncates. Conventional inserts and deletes, however, don’t as illustrated with this example. It starts with creating a table NOLOGGING and performing an insert /*+ append */ into it; the statements used are shown below:


--
-- Create a table using the NOLOGGING
-- option
--
-- It will soon be evident that not
-- all operations/inserts/deletes
-- obey that instruction
--
create table nologging_tbl(
id      number,
tname   varchar2(35),
ddl_dt  date)
nologging;

--
-- Insert data
--
-- Using 'append' allows Oracle to
-- observe the nologging aspect of
-- the table
--
insert /*+ append */ into nologging_tbl
select rownum, table_name, sysdate
from all_tables;

Using Log Miner to return the redo generated shows that no rows are returned:


SQL > --
SQL > -- Start checking which inserts and
SQL > -- deletes obey NOLOGGING
SQL > --
SQL > -- The APPEND hint inserts are nologging
SQL > --
SQL > select sql_redo
  2  from v$logmnr_contents
  3  where seg_name= upper('nologging_tbl')
  4  and sql_redo like 'insert%'
  5  /

no rows selected

SQL > 

Truncating the table also shows no redo is generated:


SQL > 
SQL > 
SQL > --
SQL > -- So is truncate, but it should be
SQL > -- since it's DDL, not DML
SQL > --
SQL > truncate table nologging_tbl;

Table truncated.

SQL > 
SQL > select sql_redo
  2  from v$logmnr_contents
  3  where seg_name= upper('nologging_tbl')
  4  and sql_redo like 'insert%'
  5  /

no rows selected

SQL > 

Now let’s execute a conventional, plain-vanilla insert (with no append hint) and see if we stil get no redo:


SQL > 
SQL > --
SQL > -- This insert will generate redo
SQL > --
SQL > -- Even though we told Oracle not
SQL > -- to for this table
SQL > --
SQL > insert into nologging_tbl
  2  select rownum, table_name, sysdate
  3  from all_tables;

2358 rows created.

SQL > 
SQL > commit;

Commit complete.

SQL > 
SQL > select sql_redo
  2  from v$logmnr_contents
  3  where seg_name = upper('nologging_tbl')
  4  and sql_redo like 'insert%'
  5  /

SQL_REDO                                                                        
--------------------------------------------------------------------------------
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('1','TYPE_MIS
C$',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                         
                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('2','ATTRCOL$
',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                           
                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('3','ASSEMBLY
$',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                          
                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('4','LIBRARY$
',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                           

insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('5','VIEWTRCO
L$',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                         
                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('6','ICOLDEP$
',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                           
                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('7','OPQTYPE$
',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                           
                                                                                
...                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('2358','YAMPO
OZA',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                        


2358 rows selected.

SQL > 

Deletes fare no better:


SQL > 
SQL > --
SQL > -- This also generates redo
SQL > --
SQL > delete from nologging_tbl;

2358 rows deleted.

SQL > 
SQL > select sql_redo
  2  from v$logmnr_contents
  3  where seg_name = upper('nologging_tbl')
  4  and sql_redo like 'delete%'
  5  /

SQL_REDO                                                                        
--------------------------------------------------------------------------------
delete from "SCOTT"."NOLOGGING_TBL" where "ID" = '704' and "TNAME" = 'WRI$_ADV_A
CTIONS' and "DDL_DT" = TO_DATE('25-MAR-19', 'DD-MON-RR') and ROWID = 'AAAX2BAAGA
AAACzAAU';                                                                      
                                                                                
delete from "SCOTT"."NOLOGGING_TBL" where "ID" = '705' and "TNAME" = 'WRI$_ADV_R
ATIONALE' and "DDL_DT" = TO_DATE('25-MAR-19', 'DD-MON-RR') and ROWID = 'AAAX2BAA
GAAAACzAAV';                                                                    
                                                                                
delete from "SCOTT"."NOLOGGING_TBL" where "ID" = '706' and "TNAME" = 'WRI$_ADV_R
EC_ACTIONS' and "DDL_DT" = TO_DATE('25-MAR-19', 'DD-MON-RR') and ROWID = 'AAAX2B
AAGAAAACzAAW';                                                                  
                                                                                
...

delete from "SCOTT"."NOLOGGING_TBL" where "ID" = '1275' and "TNAME" = 'WM$HINT_T
ABLE$' and "DDL_DT" = TO_DATE('25-MAR-19', 'DD-MON-RR') and ROWID = 'AAAX2BAAGAA
AAC/ADJ';                                                                       
                                                                                

2338 rows selected.

SQL > 

Remember that the table was created NOLOGGING and still redo was generated for selected operations. Of course the create table statement is logged so the table is recoverable by RMAN. As illustrated conventional inserts are logged, and there are at least three ways an /*+ append */ hint can be circumvented which will cause redo to be generated. The three that immediately come to mind are:


1 Row-level triggers
2 Foreign key constraints that are enabled
3 Non-unique indexes that are used to enforce unique constraints

There could be other ways the append hint will be essentially disregarded; the three listed above either convert array processing (one of the benefits of the /*+ append */ hint) into row-level processing (1,3) or impact the process by performing row-level checking (2). Of course making inserts and deletes NOLOGGING operations isn’t the best idea as that impacts recoverability. And ANY operation executed NOLOGGING in a production database is a disaster just waiting to happen. And the DBA’s job isn’t to create disaster, it’s to prevent disaster from happening.

Sometimes anywhere isn’t where you wanted to be.

Blog at WordPress.com.