Oracle Tips and Tricks — David Fitzjarrell

September 14, 2019

Unexpected Behavior

Filed under: General — dfitzjarrell @ 10:06

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

DBMS_LOGMNR is a great tool for finding information about what’s going on in an Oracle database. If it’s written to the redo logs DBMS_LOGMNR can find it. But sometimes it can find too much information, especially when assumptions are made regarding table ownership. Application code is often tested with ‘pristine’ database configurations, where ‘one table, one owner’ is the rule. Unfortunately that isn’t always the case. Let’s look at an example of how this can produce unwanted and unexpected results.

A database is configured where eight different users all have the same table, albeit with different data. Let’s use a new application that uses DBMS_LOGMNR to retrieve data changes for a given table. If the application was written to expect the ‘one owner, one table’ philosophy it could return a larger volume of data than the developers expected. In our database the APPLE table occurs eight times, once for each different owner. Let’s ‘fire up’ LogMiner and run an application query that could result from the single-owner theory and examine the results:


SQL> 
SQL> --
SQL> -- Let's start LogMiner
SQL> --
SQL> exec dbms_logmnr.start_logmnr(options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- We'll look for inserts and updates
SQL> -- to the apple table
SQL> --
SQL> -- We'll assume there's only one apple
SQL> -- table in the database owned by
SQL> -- BRAEBURN
SQL> --
SQL> -- The first query is, well, wrong
SQL> --
SQL> SELECT SEG_OWNER, OPERATION, SQL_REDO, SCN, CSCN
  2  from v$logmnr_contents
  3  where table_name = 'APPLE'
  4  AND operation in ('INSERT', 'UPDATE', 'DELETE')
  5  /
RED_DELICIOUS        INSERT         insert into "RED_DELICIOUS"."APPLE"("ID","DESCR") values ('1','Apple 1');             4590598788237 4590598788240
...
RED_DELICIOUS        INSERT         insert into "RED_DELICIOUS"."APPLE"("ID","DESCR") values ('1000','Apple 1000');       4590598788239 4590598788240
GRANNY_SMITH         INSERT         insert into "GRANNY_SMITH"."APPLE"("ID","DESCR") values ('1','Apple 1');              4590598788257 4590598788260
...
GRANNY_SMITH         INSERT         insert into "GRANNY_SMITH"."APPLE"("ID","DESCR") values ('1000','Apple 1000');        4590598788259 4590598788260
PINK_LADY            INSERT         insert into "PINK_LADY"."APPLE"("ID","DESCR") values ('1','Apple 1');                 4590598788278 4590598788281
...
PINK_LADY            INSERT         insert into "PINK_LADY"."APPLE"("ID","DESCR") values ('1000','Apple 1000');           4590598788280 4590598788281
BRAEBURN             INSERT         insert into "BRAEBURN"."APPLE"("ID","DESCR") values ('1','Apple 1');                  4590598788300 4590598788303
...
BRAEBURN             INSERT         insert into "BRAEBURN"."APPLE"("ID","DESCR") values ('1000','Apple 1000');            4590598788302 4590598788303
GOLDEN_DELICIOUS     INSERT         insert into "GOLDEN_DELICIOUS"."APPLE"("ID","DESCR") values ('1','Apple 1');          4590598788320 4590598788323
...
GOLDEN_DELICIOUS     INSERT         insert into "GOLDEN_DELICIOUS"."APPLE"("ID","DESCR") values ('1000','Apple 1000');    4590598788322 4590598788323
GALA                 INSERT         insert into "GALA"."APPLE"("ID","DESCR") values ('1','Apple 1');                      4590598788343 4590598788347
...
GALA                 INSERT         insert into "GALA"."APPLE"("ID","DESCR") values ('1000','Apple 1000');                4590598788346 4590598788347
ROME                 INSERT         insert into "ROME"."APPLE"("ID","DESCR") values ('1','Apple 1');                      4590598788365 4590598788368
...
ROME                 INSERT         insert into "ROME"."APPLE"("ID","DESCR") values ('1000','Apple 1000');                4590598788367 4590598788368
HONEYCRISP           INSERT         insert into "HONEYCRISP"."APPLE"("ID","DESCR") values ('1','Apple 1');                4590598788385 4590598788388
...
HONEYCRISP           INSERT         insert into "HONEYCRISP"."APPLE"("ID","DESCR") values ('1000','Apple 1000');          4590598788387 4590598788388

8000 rows selected.

SQL> 

The application wasn’t expect 8000 rows of data since only 1000 were inserted into the APPLE table owned by BRAEBURN. The issue with the query is that the segment owner was not included in the WHERE clause; had there only been one APPLE table then the results would have been correct, that is until another user creates an APPLE table. When that happens LogMiner won’t know to search only for the BRAEBURN copy and, as can be seen by the results above, will return all inserts, updates and deletes for every table named APPLE in the database. If the application is using those results to process data for the BRAEBURN schema there will be a very large number of errors for ‘bad data’.

Let’s try this again, this time restricting the query to only the BRAEBURN schema:


SQL> 
SQL> --
SQL> -- There's MUCH more data here than
SQL> -- we expected
SQL> --
SQL> -- Let's try again and tell LogMiner
SQL> -- whose table to look for
SQL> --
SQL> 
SQL> SELECT SEG_OWNER, OPERATION, SQL_REDO, SCN, CSCN
  2  from v$logmnr_contents
  3  where table_name = 'APPLE'
  4  and seg_owner = 'BRAEBURN'
  5  AND operation in ('INSERT', 'UPDATE', 'DELETE')
  6  /
BRAEBURN             INSERT         insert into "BRAEBURN"."APPLE"("ID","DESCR") values ('1','Apple 1');                  4590598788300 4590598788303
...
BRAEBURN             INSERT         insert into "BRAEBURN"."APPLE"("ID","DESCR") values ('1000','Apple 1000');            4590598788302 4590598788303

1000 rows selected.

SQL> 

Now the query returns the expected results, and any processing done with that data will (hopefully) not encounter errors.

Assumptions can create unforseen problems in applications, especially when those applications touch a database. As mentioned previously not every database is built on the ‘one owner, one table’ mantra and it’s a very good idea to test code where that condition does not exist. Doing so can prevent any number of confusing mishaps.

If you expect something, make sure you get what you expected.

Advertisements

August 29, 2019

It’s A Date?

Filed under: General — dfitzjarrell @ 16:09

"You often learn more by being wrong for the right reasons
than right for the wrong reasons." 
― Norton Juster, The Phantom Tollbooth

Dates. The term can conjur fear in the strongest hearts, mainly due to problems that arise when date formats are taken for granted. Not every Oracle database is configured with the ‘default’ Oracle-supplied date format, DD-MON-RR. Since this setting can be changed at both the database level and the session level it can be rather confusing when scripts aren’t working properly. Let’s see what can go horribly wrong when date formats are taken for granted or ignored.

Implicit conversion is the most insidious in my opinion as it relies on the declared format for the database or the session, and that is defined by the NLS_DATE_FORMAT parameter. Date strings come in all shapes and sizes but for an Oracle database the format it’s expecting may not be the format the string is in. Given this NLS_DATE_FORMAT setting:.


DD-MON-RRRR

any attempt to implicitly convert ‘1999-01-31’ to an Oracle date will fail:


SQL> select * From dba_Objects where created = '1999-01-31';
select * From dba_Objects where created = '1999-01-31'
                                          *
ERROR at line 1:
ORA-01861: literal does not match format string


SQL>

If the string is put into a more … ‘normal’ … format a different error appears:


SQL$gt; select * From dba_Objects where created = '01-01-1999'
  2  /
select * From dba_Objects where created = '01-01-1999'
                                          *
ERROR at line 1:
ORA-01843: not a valid month


SQL>

To avoid such errors best practice is to always use TO_DATE() to convert strings into date values and always supply a matching format string. It is not a good practice to rely on session or database settings even if you KNOW the formats are the same. Someone else that might execute that script may not have set the same date format used in your session, and mystically the script fails to execute with one of the above errors. As another example let’s set the NLS_DATE_FORMAT in a session to a known string and perform some conversions. The session date format will be changed twice; for some conversions either format provides error-free results but when the month and day are reversed some conversions will complete but the month portion of the date could be a surprise::


SQL> --
SQL> -- Set the session date format
SQL> -- the same as the string we are trying
SQL> -- to convert
SQL> --
SQL> -- Notice the month is first in the format
SQL> -- string
SQL> --
SQL> alter session set nls_date_format = 'MM-DD-RRRR';

Session altered.

SQL>
SQL> --
SQL> -- Try implicit conversion again
SQL> --
SQL> -- Return the month name as well
SQL> --
SQL> -- This works but it's 'iffy'
SQL> --
SQL> -- Relying on the default format is
SQL> -- not a wise idea
SQL> --
SQL> select '01-01-1999' from dual
  2  where sysdate >= '01-01-1999';

'01-01-199
----------
01-01-1999

SQL>
SQL> select to_char(to_date('01-01-1999'),'Month') from dual;

TO_CHAR(T
---------
January

SQL>
SQL> --
SQL> -- Another attempt using TO_DATE()
SQL> -- without a format string
SQL> --
SQL> -- Again, this works but it's 'iffy'
SQL> --
SQL> select to_date('01-01-1999') from dual;

TO_DATE('0
----------
01-01-1999

SQL>
SQL> select to_char(to_date('01-01-1999'),'Month') from dual;

TO_CHAR(T
---------
January

SQL>
SQL> --
SQL> -- Use a properly constructed TO_DATE()
SQL> -- statement
SQL> --
SQL> -- This works without exception
SQL> --
SQL> select to_date('01-31-1999', 'MM-DD-RRRR') from dual;

TO_DATE('0
----------
01-31-1999

SQL>
SQL> --
SQL> -- Change the session format yet again
SQL> --
SQL> -- This time the month is not first
SQL> -- so conversions where the 'day' is 12
SQL> -- or less will still work with this format
SQL> --
SQL> alter session set nls_date_format = 'DD-MM-RRRR';

Session altered.

SQL>
SQL> --
SQL> -- Change the date string to convert
SQL> --
SQL> -- Even with the month and day reversed the
SQL> -- following conversions, implicit and unformatted,
SQL> -- work because there is a month number 1 in
SQL> -- the year
SQL> --
SQL> -- This won't throw any errors until the day
SQL> -- is greater than 12
SQL> --
SQL> -- In this case the month is what we expect
SQL> --
SQL> select '01-01-1999' from dual
  2  where sysdate >= '01-01-1999';

'01-01-199
----------
01-01-1999

SQL>
SQL> select to_date('01-01-1999') from dual;

TO_DATE('0
----------
01-01-1999

SQL>
SQL> select to_char(to_date('01-01-1999'),'Month') from dual;

TO_CHAR(T
---------
January

SQL>
SQL> --
SQL> -- Let's make the 'day' value 11
SQL> -- and see what date results
SQL> --
SQL> -- We won't get the month we
SQL> -- expect
SQL> --
SQL> select '01-11-1999' from dual
  2  where sysdate >= '01-11-1999';

'01-11-199
----------
01-11-1999

SQL>
SQL> select to_char(to_date('01-11-1999'),'Month') from dual;

TO_CHAR(T
---------
November

SQL>
SQL> --
SQL> -- Now the implicit and unformatted
SQL> -- conversions fail
SQL> --
SQL> -- The default format switched the month
SQL> -- and day in the string
SQL> --
SQL> -- The Gregorian calendar doesn't have
SQL> -- 31 months in a year
SQL> --
SQL> select '01-31-1999' from dual
  2  where sysdate >= '01-31-1999';
where sysdate >= '01-31-1999'
                 *
ERROR at line 2:
ORA-01843: not a valid month


SQL>
SQL> select to_date('01-31-1999') from dual;
select to_date('01-31-1999') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL>
SQL> --
SQL> -- The formatted conversion still works
SQL> --
SQL> select to_date('01-31-1999', 'MM-DD-RRRR') from dual;

TO_DATE('0
----------
31-01-1999

SQL>

Not knowing the pre-set NLS_DATE_FORMAT can wreak havoc with implicit date conversions, and do the same when TO_DATE() is used without a format specifier. Depending upon the format string it may take a while for Oracle to throw an error and the reason may seem to be a mystery. Not many applications return the month name from a date value so when the format starts ‘DD-MM’ rather than ‘MM-DD’ errors won’t appear for date strings similar to ’01-01-2020′ until day 13 of the month, leaving developers to scratch their heads in disbelief and confusion.

Good coding practice is to never rely on default date formats; always code conversion functions with a date format specifier so that generated dates are never in doubt. And never rely on implicit date conversions as each database configuration is different. The goal is to write portable code that will run on any installation of Oracle the code could encounter (barring functional changes due to version differences). Being lazy with dates can result in broken code and applications that abort seemingly without reason. Don’t let this happen to you.

Be right for the RIGHT reasons.

 

June 20, 2019

“Just One More…”

Filed under: General — dfitzjarrell @ 13:54

"So each one of you agrees to disagree with whatever the other
one agrees with, but if you both disagree with the same thing,
aren't you really in agreement?" 
― Norton Juster, The Phantom Tollbooth

Partitions can make DBA life easier; they allow faster access to data through partition pruning and they allow for easier archiving and removal of old data. There’s not much that can go wrong with partitions [ominous silence]… until you want to create an interval-partitioned table with hash subpartitions and you want use that strategy by creating daily partitions with 1,024 subpartitions. Let’s look at why that plan won’t work, but first let’s look at some logical database limits Oracle imposes on partitions and subpartitions.

The following table lists the logical limits that affect such a strategy:

Partitions Maximum number of partitions allowed per table or index 1024K – 1
Subpartitions Maximum number of subpartitions in a composite partitioned table 1024K – 1

Notice the maximum number of partitions is 1048575. Since we are creating a composite partitioned table the maximum number of subpartitions is also 1048575, which means when you compute the total subpartitions for the entire table it cannot be greater than 1048575. For this example each partition will have 1024 subpartitions, which limits the number of partitions we can create to 1023, since we cannot have a partial partition. Yes, 1023 partitions is a very large number of partitions, and with 365 days in a year that allows for 2.8 years of data in the table, again quite a lot of daily data to be stored. Processes can run happily along until the table reaches that magic number and things start to fail. The example, below, has been modified from one provided by Jonathan Lewis; notice the various errors it throws when logical partition/subpartition limits are reached:


SQL> 
SQL> set verify off echo on
SQL> column table_name format a20
SQL> column partition_name format a20
SQL> column subpartition_name format a20
SQL> column high_value format a20
SQL> column part_end_dt new_value startdt
SQL> select to_char(trunc(sysdate),'dd-mon-yyyy') part_end_dt from dual;

PART_END_DT
--------------------
02-mar-2016

SQL> 
SQL> create table hash_by_interval (
  2          int_dt         date            not null,
  3          num_val        number(10,0)    not null,
  4          strng_dat      varchar2(100)
  5  )
  6  tablespace assm_int_part_test
  7  partition by range (int_dt) interval (numtodsinterval(1,'DAY'))
  8  subpartition by hash (num_val) subpartitions 1024
  9          (
 10                  partition ip0 values less than (to_date('&startdt','dd-mon-yyyy'))
 11          )
 12  ;

Table created.

SQL> 
SQL> insert into hash_by_interval values (trunc(sysdate) + 1023, 1, rpad('x',100));
insert into hash_by_interval values (trunc(sysdate) + 1023, 1, rpad('x',100))
            *
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions


SQL> insert into hash_by_interval values (trunc(sysdate) + 1022, 1, rpad('x',100));
insert into hash_by_interval values (trunc(sysdate) + 1022, 1, rpad('x',100))
*
ERROR at line 1:
ORA-14299: total number of partitions/subpartitions exceeds the maximum limit


SQL> insert into hash_by_interval values (trunc(sysdate) + 1021, 1, rpad('x',100));

1 row created.

SQL> commit;

Commit complete.

SQL> 

The first attempted insert in the example fails immediately, the second takes a while but it also fails. On the face of it the errors seem to be saying the same thing, but they’re not. Going back to the logical limits the maximum number of partitions is 1048575; the first insert error reports that the partitioning key would map to a partition outside the maximum partition limit, which would create a partition numbered 1048576, clearly a violation of the logical limits. The second insert error reports that it should go into partition number 1025, which is not a violation of the number of subpartitions for a single partition but it does violate the total subpartitions limit by creating a total of 1048576 subpartitions. They aren’t the same condition so they generate different errors. Both conditions result in the same problem: no data for the current day in the partitioned table, which may be confusing.

For the test table a few numbers:


SQL> 
SQL> select table_name, partition_name, subpartition_count
  2  from user_tab_partitions;

TABLE_NAME           PARTITION_NAME       SUBPARTITION_COUNT
-------------------- -------------------- ------------------
HASH_BY_INTERVAL     SYS_P45714                         1024
HASH_BY_INTERVAL     IP0                                1024

SQL> 
SQL> select table_name, partitioning_type, subpartitioning_type, partition_count, def_subpartition_count, subpartitioning_key_count
  2  from user_part_tables;

TABLE_NAME           PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT SUBPARTITIONING_KEY_COUNT
-------------------- --------- --------- --------------- ---------------------- -------------------------
HASH_BY_INTERVAL     RANGE     HASH              1048575                   1024                         1

SQL> 
SQL>

For interval partitioned tables Oracle sets the partition count to the maximum limit, 1048575. For hash and range partitioned tables the partition count is the actual number of partitions in the table. An interesting fact emerges with interval-partitioned tables: the first partition is always a RANGE partition, after which the interval partitioning takes over. This is why we see two partitions in the first set of query results shown above.

This example shows how important it is to have a partition archiving process in place for such tables; archiving older partitions by converting them into stand-alone tables provides the necessary ‘room’ to keep this strategy going by ensuring that the table will never reach, much less try to exceed, the logical limits on partition/subpartition counts enforced by Oracle.

Partitioning, when given careful thought and robust testing, can be extremely beneficial in both query performance and data management. Thorough testing is the key, especially at the extreme limits for the data. Devising tests that take Oracle to its logical limits may not be easy, but in cases like this they’re necessary to plan for, and, hopefully eliminate, issues that can stop production in its tracks.

I expect that we are in agreement.

June 4, 2019

“Hear ye, hear ye!”

Filed under: General — dfitzjarrell @ 18:21

"Have you ever heard the wonderful silence just before the dawn? Or the quiet
and calm just as a storm ends? Or perhaps you know the silence when you haven't
the answer to a question you've been asked, or the hush of a country road at
night, or the expectant pause of a room full of people when someone is just
about to speak, or, most beautiful of all, the moment after the door closes
and you're alone in the whole house? Each one is different, you know, and all
very beautiful if you listen carefully."
-- Norton Juster, The Phantom Tollbooth

In versions of Oracle prior to 12.2 the Listener has been more forgiving with static registrations; with 12.2 and later releases that seems to have changed as static registrations, if not configured completely, can cause the listener to dump a core file in the directory where the listener process was started. What can make this even more confusing is that dynamic registrations are not affected. And to add to all of this interesting behavior having databases down when starting the listener doesn’t ‘fix’ the problem because the listener isn’t connecting to a running database, it’s trying to resolve library paths for static declarations. Let’s look at how static registrations can be written to avoid this (and allow one to keep his or her hair and sanity in the process).

Statis registrations for TCP connections are the ‘problem’ with the 12.2 listener (from personal experience) when they stop short of configuring the library path for the given database and home. A typical static TCP registration can look like this:


SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = smerma)
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = alpoz)
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = plorg)
    )
)

Even up to release 12.1.0.2 the listener would appear to ‘pick up’ the necessary environment settings and find the associated libraries in the given ORACLE_HOME; in 12.2.0.1 this seems to have changed which causes the core file to be generated for Oracle databases not using the same home as the running listener. This can be a problem if your site has a dedicated home for the listener to run from since no databases will be running from that home. And, not knowing that static registrations are at fault the core file geenration will be a mystery.

The solution to this is fairly simple and has been available in listener configurations for many releases; of course having a dedicated listener home is not a standard configuration and, as such, this issue may never rear its ugly head. Modifying the configuration shown above to provide the ‘missing’ information the listener needs is merely a matter of adding the ENVS configuration parameter for each static database registration, as shown below:


SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = smerma)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/lib')
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = alpoz)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/lib')
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = plorg)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/lib')
    )
)

It is a simple change that may be unfamiliar to some DBAs who have never configured a listener outside of NETCA, and if static registrations are not required at a site the DBA may not have had the opportunity to even see a listener.ora [the listener will run just fine without a listener.ora file, but it will use the default port of 1521]. Of course simply changing the listener.ora doesn’t fix anything in a currently running listener as it will need to be reloaded to make the changes effective:


$ lsnrctl reload listener

LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 07-JAN-2018 14:39:24

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.mydomain.com)(PORT=1522)))
The command completed successfully
$

If your listener is named LISTENER (the default naming provided by NETCA) then a simple:


lsnrctl reload

is all that’s necessary. It is good practice, however, to supply the listener name every time one performs maintenance on a listener so the correct listener is affected. Not doing so may result in no actual reload taking place as the current lsnrctl executable won’t ‘know’ which LISTENER to reload on systems where multiple Oracle installations exist. In situations where LISTENER is not the name of the running listener (creativity may run amok and it may be named LISTENER_BOB, LISTENER_TURNIP or even LISTENER_POODLE) there won’t be any listener to act upon.

Every static registration can have its own ENVS setting; in installations involving multiple Oracle versions all using a single listener it’s perfectly legal to have each ENVS entry specify a different lib location:


SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = smerma)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/lib')
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle/12)
      (SID_NAME = alpoz)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/12/lib')
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle/10)
      (SID_NAME = plorg)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/10/lib')
    )
)

[This may not be obvious to DBAs not familiar with static configurations created by writing or editing a listener.ora file. If standby databases are not in use or if database backups are executed locally with RMAN then static registrations may not be necessary.]

For any DBA not familiar with static database registrations they exist so that the database can be succesfully connected to even when the database is not running. Static registrations are indicated by UNKNOWN when the listener status is generated:


[oracle@e3c57ee3dc64 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 04-JUN-2019 18:05:55

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=e3c57ee3dc64)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                04-JUN-2019 14:08:31
Uptime                    0 days 9 hr. 57 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/e3c57ee3dc64/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=e3c57ee3dc64)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=e3c57ee3dc64)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl" has 2 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@e3c57ee3dc64 ~]$

The UNKNOWN status is not an error; it simply indicates that the connection is manually configured and the listener cannot possibly know if the database is up or down, nor does it care. Having such a configuration makes it possible to run remote ‘cold’ backups and to run Data Guard databases by allowing remote startup and switchover of the database and standby.

Sometimes the most confusing issues can have the simplest of solutions; fortunately for listener static configurations this is the case as it only requires the addition of the ENVS parameter to provide the information the listener process was missing. This is, of course, covered in the Oracle on-line documentation (for several releases) which makes a good reason to have such documentation bookmarked in the browser.

You are listening?

May 11, 2019

Union Station

Filed under: General — dfitzjarrell @ 10:00

"Since you got here by not thinking, it seems reasonable to expect that,
in order to get out, you must start thinking."
-- Norton Juster, The Phantom Tollbooth

Combining two data sets can go in several directions and, in the absence of common columns, conventional wisdom would point to using UNION to generate such sets and for many applications this is a suitable solution. If, however, these unions are the ‘table’ for another query, one intending to produce distinct results, simply diving in and using UNION may not be the best direction to take.

The following example has been specifically written to highlight an issue when using UNION; it is similar to actual code I was asked to tune (although the actual code was not intentionally written to generate SORT UNIQUE operations as this was) and illustrates the issue that was slowing down execution. Let’s begin by creating three simple two-column tables:


BLEEPLE @ smang > create table tab1(
  2  snord   number,
  3  queebo  varchar2(40));

Table created.

BLEEPLE @ smang > 
BLEEPLE @ smang > create table tab2(
  2  plang   number,
  3  horbst  varchar2(40));

Table created.

BLEEPLE @ smang > 
BLEEPLE @ smang > create table tab3(
  2  umo     number,
  3  erst    varchar2(40));

Table created.

BLEEPLE @ smang > 

To speed up the resulting queries function-based indexes will be created and the tables will be populated. Finally statistics will be gathered on each table and index::


BLEEPLE @ smang > create index tab1_fbi on tab1(mod(snord,970));

Index created.

BLEEPLE @ smang > create index tab2_fbi on tab2(mod(plang,970));

Index created.

BLEEPLE @ smang > create index tab3_fbi on tab3(mod(umo,970));

Index created.

BLEEPLE @ smang > 
BLEEPLE @ smang > begin
  2  	     for i in 1..1000000 loop
  3  		     insert into tab1
  4  		     values(i, 'Eebeeneebee'||i);
  5  		     if mod(i,970) = 0 then
  6  			     insert into tab2
  7  			     values(i, 'Ooboonooboo'||i);
  8  			     insert into tab3
  9  			     values(i, 'Uubuunuubuu'||i);
 10  		     else
 11  			     insert into tab2
 12  			     values(i, 'Eebeeneebee'||i);
 13  			     insert into tab3
 14  			     values(i, 'Eebeeneebee'||i);
 15  		     end if;
 16  	     end loop;
 17  
 18  	     commit;
 19  end;
 20  /

PL/SQL procedure successfully completed.

BLEEPLE @ smang > 
BLEEPLE @ smang > exec dbms_stats.gather_table_stats(user, 'TAB1', cascade=>true);

PL/SQL procedure successfully completed.

BLEEPLE @ smang > exec dbms_stats.gather_table_stats(user, 'TAB2', cascade=>true);

PL/SQL procedure successfully completed.

BLEEPLE @ smang > exec dbms_stats.gather_table_stats(user, 'TAB3', cascade=>true);

PL/SQL procedure successfully completed.

BLEEPLE @ smang > 

So far, so good. Now, let’s generate a result set using UNION and see how Oracle processes that request:


BLEEPLE @ smang > set autotrace on
BLEEPLE @ smang > 
BLEEPLE @ smang > with u1 as(
  2  select snord, queebo From tab1
  3   where mod(snord,970) = 0
  4  union
  5  select plang, horbst From tab2
  6   where mod(plang,970) = 0
  7  ),
  8  u2 as (
  9  select plang, horbst From tab2
 10   where mod(plang,970) = 0
 11  union
 12  select umo, erst  from tab3
 13  where mod(umo,970) = 0
 14  )
 15  select distinct t.* from
 16  (select * from u1 union select * from u2) t;

     SNORD QUEEBO                                                                                                                           
---------- ----------------------------------------                                                                                         
       970 Eebeeneebee970                                                                                                                   
       970 Ooboonooboo970                                                                                                                   
       970 Uubuunuubuu970                                                                                                                   
      1940 Eebeeneebee1940                                                                                                                  
      1940 Ooboonooboo1940                                                                                                                  
      1940 Uubuunuubuu1940                                                                                                                  
      2910 Eebeeneebee2910                                                                                                                  
      2910 Ooboonooboo2910                                                                                                                  
      2910 Uubuunuubuu2910                                                                                                                  
      3880 Eebeeneebee3880                                                                                                                  
      3880 Ooboonooboo3880                                                                                                                  
...
    996190 Uubuunuubuu996190                                                                                                                
    997160 Eebeeneebee997160                                                                                                                
    997160 Ooboonooboo997160                                                                                                                
    997160 Uubuunuubuu997160                                                                                                                
    998130 Eebeeneebee998130                                                                                                                
    998130 Ooboonooboo998130                                                                                                                
    998130 Uubuunuubuu998130                                                                                                                
    999100 Eebeeneebee999100                                                                                                                
    999100 Ooboonooboo999100                                                                                                                
    999100 Uubuunuubuu999100                                                                                                                

3090 rows selected.


Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 2741225135                                                                                                                 
                                                                                                                                            
------------------------------------------------------------------------------------------------------                                      
| Id  | Operation                                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                                      
------------------------------------------------------------------------------------------------------                                      
|   0 | SELECT STATEMENT                          |          |  4109 |   140K|  4155   (1)| 00:00:01 |                                      
|   1 |  VIEW                                     |          |  4109 |   140K|  4155   (1)| 00:00:01 |                                      
|   2 |   SORT UNIQUE                             |          |  4109 |   140K|  4155   (1)| 00:00:01 |                                      
|   3 |    UNION-ALL                              |          |       |       |            |          |                                      
|   4 |     VIEW                                  |          |  2048 | 71680 |  2076   (1)| 00:00:01 |                                      
|   5 |      SORT UNIQUE                          |          |  2048 | 55296 |  2076   (1)| 00:00:01 |                                      
|   6 |       UNION-ALL                           |          |       |       |            |          |                                      
|   7 |        TABLE ACCESS BY INDEX ROWID BATCHED| TAB1     |  1018 | 27486 |  1037   (0)| 00:00:01 |                                      
|*  8 |         INDEX RANGE SCAN                  | TAB1_FBI |  1031 |       |     6   (0)| 00:00:01 |                                      
|   9 |        TABLE ACCESS BY INDEX ROWID BATCHED| TAB2     |  1030 | 27810 |  1037   (0)| 00:00:01 |                                      
|* 10 |         INDEX RANGE SCAN                  | TAB2_FBI |  1031 |       |     6   (0)| 00:00:01 |                                      
|  11 |     VIEW                                  |          |  2061 | 72135 |  2076   (1)| 00:00:01 |                                      
|  12 |      SORT UNIQUE                          |          |  2061 | 55647 |  2076   (1)| 00:00:01 |                                      
|  13 |       UNION-ALL                           |          |       |       |            |          |                                      
|  14 |        TABLE ACCESS BY INDEX ROWID BATCHED| TAB2     |  1030 | 27810 |  1037   (0)| 00:00:01 |                                      
|* 15 |         INDEX RANGE SCAN                  | TAB2_FBI |  1031 |       |     6   (0)| 00:00:01 |                                      
|  16 |        TABLE ACCESS BY INDEX ROWID BATCHED| TAB3     |  1031 | 27837 |  1037   (0)| 00:00:01 |                                      
|* 17 |         INDEX RANGE SCAN                  | TAB3_FBI |  1031 |       |     6   (0)| 00:00:01 |                                      
------------------------------------------------------------------------------------------------------                                      
                                                                                                                                            
Predicate Information (identified by operation id):                                                                                         
---------------------------------------------------                                                                                         
                                                                                                                                            
   8 - access(MOD("SNORD",970)=0)                                                                                                           
  10 - access(MOD("PLANG",970)=0)                                                                                                           
  15 - access(MOD("PLANG",970)=0)                                                                                                           
  17 - access(MOD("UMO",970)=0)                                                                                                             


Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
       7230  consistent gets                                                                                                                
       3055  physical reads                                                                                                                 
     247332  redo size                                                                                                                      
     115568  bytes sent via SQL*Net to client                                                                                               
       2807  bytes received via SQL*Net from client                                                                                         
        207  SQL*Net roundtrips to/from client                                                                                              
          3  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
       3090  rows processed                                                                                                                 

BLEEPLE @ smang > 

Notice lines 2, 5 and 12 of the execution plan — all three are SORT UNIQUE, one for each of the interim result sets and the last for the combined set of data. Only one SORT UNIQUE was expected when this code was written. Implicit sorts from the UNION operators added the other two to the plan. Let’s modify this code a bit and use UNION ALL in place of the UNION statements originally used:


BLEEPLE @ smang > 
BLEEPLE @ smang > with u1 as(
  2  select snord, queebo From tab1
  3   where mod(snord,970) = 0
  4  union all
  5  select plang, horbst From tab2
  6   where mod(plang,970) = 0
  7  ),
  8  u2 as (
  9  select plang, horbst From tab2
 10   where mod(plang,970) = 0
 11  union all
 12  select umo, erst  from tab3
 13  where mod(umo,970) = 0
 14  )
 15  select distinct t.* from
 16  (select * from u1 union all select * from u2) t;

     SNORD QUEEBO                                                                                                                           
---------- ----------------------------------------                                                                                         
      1940 Eebeeneebee1940                                                                                                                  
     11640 Eebeeneebee11640                                                                                                                 
     20370 Eebeeneebee20370                                                                                                                 
     23280 Eebeeneebee23280                                                                                                                 
     25220 Eebeeneebee25220                                                                                                                 
...
    978730 Uubuunuubuu978730                                                                                                                
    956420 Uubuunuubuu956420                                                                                                                
    982610 Uubuunuubuu982610                                                                                                                
    389940 Uubuunuubuu389940                                                                                                                
    303610 Uubuunuubuu303610                                                                                                                
    312340 Uubuunuubuu312340                                                                                                                
    301670 Uubuunuubuu301670                                                                                                                
    352110 Uubuunuubuu352110                                                                                                                
    349200 Uubuunuubuu349200                                                                                                                
    392850 Uubuunuubuu392850                                                                                                                

3090 rows selected.


Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 294584558                                                                                                                  
                                                                                                                                            
-----------------------------------------------------------------------------------------------------                                       
| Id  | Operation                                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                                       
-----------------------------------------------------------------------------------------------------                                       
|   0 | SELECT STATEMENT                         |          |  4109 |   140K|  4150   (1)| 00:00:01 |                                       
|   1 |  HASH UNIQUE                             |          |  4109 |   140K|  4150   (1)| 00:00:01 |                                       
|   2 |   VIEW                                   |          |  4109 |   140K|  4149   (1)| 00:00:01 |                                       
|   3 |    UNION-ALL                             |          |       |       |            |          |                                       
|   4 |     VIEW                                 |          |  2048 | 71680 |  2074   (0)| 00:00:01 |                                       
|   5 |      UNION-ALL                           |          |       |       |            |          |                                       
|   6 |       TABLE ACCESS BY INDEX ROWID BATCHED| TAB1     |  1018 | 27486 |  1037   (0)| 00:00:01 |                                       
|*  7 |        INDEX RANGE SCAN                  | TAB1_FBI |  1031 |       |     6   (0)| 00:00:01 |                                       
|   8 |       TABLE ACCESS BY INDEX ROWID BATCHED| TAB2     |  1030 | 27810 |  1037   (0)| 00:00:01 |                                       
|*  9 |        INDEX RANGE SCAN                  | TAB2_FBI |  1031 |       |     6   (0)| 00:00:01 |                                       
|  10 |     VIEW                                 |          |  2061 | 72135 |  2074   (0)| 00:00:01 |                                       
|  11 |      UNION-ALL                           |          |       |       |            |          |                                       
|  12 |       TABLE ACCESS BY INDEX ROWID BATCHED| TAB2     |  1030 | 27810 |  1037   (0)| 00:00:01 |                                       
|* 13 |        INDEX RANGE SCAN                  | TAB2_FBI |  1031 |       |     6   (0)| 00:00:01 |                                       
|  14 |       TABLE ACCESS BY INDEX ROWID BATCHED| TAB3     |  1031 | 27837 |  1037   (0)| 00:00:01 |                                       
|* 15 |        INDEX RANGE SCAN                  | TAB3_FBI |  1031 |       |     6   (0)| 00:00:01 |                                       
-----------------------------------------------------------------------------------------------------                                       
                                                                                                                                            
Predicate Information (identified by operation id):                                                                                         
---------------------------------------------------                                                                                         
                                                                                                                                            
   7 - access(MOD("SNORD",970)=0)                                                                                                           
   9 - access(MOD("PLANG",970)=0)                                                                                                           
  13 - access(MOD("PLANG",970)=0)                                                                                                           
  15 - access(MOD("UMO",970)=0)                                                                                                             


Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
       4140  consistent gets                                                                                                                
        213  physical reads                                                                                                                 
          0  redo size                                                                                                                      
     113955  bytes sent via SQL*Net to client                                                                                               
       2807  bytes received via SQL*Net from client                                                                                         
        207  SQL*Net roundtrips to/from client                                                                                              
          0  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
       3090  rows processed                                                                                                                 

BLEEPLE @ smang > 

The three SORT UNIQUE operations have now been replaced with a single HASH UNIQUE operation. If the result sets are examined it will be noticed that the SORT UNIQUE results are ordered by the leading column of the table and the HASH UNIQUE results appear to be haphazard. Believe it or not the hash results are ordered; they are ordered by the hash key used to ‘weed out’ the distinct elements. In previous releases of Oracle (9 and earlier) the three SORT UNIQUE statements would be replaced by a single SORT UNIQUE from the ‘select distinct’ query. Nonetheless using UNION ALL does produce a less resource-intensive plan and returns the same data, even if not in the same physical order.

It may not be possible to give such problems much thought in this day and age because someone wants the results … NOW. But, the more you know about how Oracle behaves can make such work easier in the long run.

And that is worth thinking about.

May 7, 2019

Alphabet Soup

Filed under: General — dfitzjarrell @ 18:38

"Ali zar u buci nema i dobrih zvukova?" 
-- Norton Juster, The Phantom Tollbooth

Disaster recovery is on the mind of every Oracle DBA, the goal being that there is never a need to use it. Setting optimism aside, many enerprises run regular DR tests to ensure that business can continue as usual should a catastrophic event affect the data center. Before the advent of Oracle Managed Files a ‘proper’ DR onfiguration included generating scripts to create controfiles, to replace the standby controlfile at the DR site so the standby can be opened and used as a ‘regular’ database. When Oracle Managed Files are in use the situation changes. Let’s look at what OMF does and why a script to create the controlfile can’t originate from the primary database.

Oracle Managed Files, for those not familiar with them, are dynamically named based on the tablespace and use a generated value to uniquely identify them in the database. Each database installation will generate unique identifiers, and such identifiers won’t match between primary and standby databases. Thus a ‘create controlfile’ statement from the primary database won’t use the local file names generated from the standby and no controlfile will be created. Let’s look at an example using a primary databaase (prim_db) and it’s associated standby (stby_db).

PRIM_DB uses Oracle Managed Files to make it easier on the DBA to add data files. This is configured using the db_create_file_dest parameter:


SQL$gt; show parameter create_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /spatula
SQL>

So, for PRIM_DB all datafiles will be located at /spatula/PRIM_DB/datafile and will have uniquely generated file names:


...
o1_mf_sysaux_fsf2fb5y_.dbf
o1_mf_system_fsf2dkw2_.dbf
...

The standby database is also configured to use OMF; looking at the file names for SYSTEM and SYSAUX for that database we see:


...
o1_mf_sysaux_g02yt5l0_.dbf
o1_mf_system_g02yqhl6_.dbf
...

As mentioned previously the unique identifiers will not match between databases; a controlfile creation script generated on the primary will therefore be unusable on the standby. In this case the script generation procedures need to shift to the standby server so that a usable create controlfile script can be generated. It should not be necessary to copy the init.ora file from the primary to the standby as minimal changes will be required to run the new primary from the standby location. Those changes are:


db_unique_name
local_listener
log_archive_dest_1

Having a second init.ora file, named init_prim.ora with these changes, will make it easy to start the new primary so the controlfile can be created.

Having a current create controlfile script ready and waiting is simple and straightforward using the ‘alter database backup controfile to trace’ command; as an example such a command is shown below:


set echo on
spool /spatula/oracle/sql/stby_db_ctlfile.out
alter database backup controlfile to trace as '/home/oracle/recov_inits/stby_db_ccf.txt' reuse resetlogs;
spool off
exit

The ‘create controlfile’ script created will generate one CREATE CONTROLFILE statement rather than two, restricting the output to the REEETLOGS option and reusing the existing controlfile name. [Note that a ‘normal’ create controlfile script will generate two statements, a RESETLOGS option and a NORESETLOGS option, and in the case of a DR exercise the RESETLOGS option is the one that is used, thus the restriction.] Since this statement is generated from the standby the file names will be correctly specified and the new, regular controlfile will create successfully.

Using OMF also changes how the TEMP files are created; since file and path names are no longer required when creating such files the command is simplified:


SQL> alter tablespace temp add tempfile size [value];

The same command can be executed multiple times to create as many tempfiles as necessary.

Using OMF with Data Guard configurations changes how controlfiles are created but it isn’t a difficult change to implement. It also eliminates issues across network links as the files are created locally. Create a location to store them and set up a cron task to execute the script on a weekly basis (to catch any new datafiles created on the primary) and should the need arise to activate the standby the task should be a fairly simple one.

And the DBA won’t be swimming through alphabet soup to get there.

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.

March 10, 2019

Final Elimination

Filed under: General — dfitzjarrell @ 08:07

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

Occasionally a join using primary key columns in a query can be eliminated because of the data involved. If Oracle determines that all of the ‘required’ data is available in a single table the optimizer will rewrite the query to eliminate the ‘unnecessary’ join. Oracle has been doing this for years, with the limitation that the primary keys involved were single-column keys. In versions 12.2 and later multi-column primary keys can be used, however there are cases where the join elimination isn’t complete and that can depend on table order in the FROM clause or on the join syntax in use. Let’s look at an example that illustrates these points.

This example uses three tables — my_table, my_other_table, and my_other_other_table — in various parent->child relationships. [Building the tables won’t be covered here, as we are interested only in the join elimination results.] Using the traditional Oracle syntax for the joins we find that the table order can affect the extent of the join elimination. Remember that the goal is to reduce the query to its simplest form having a minimum of join conditions (ideally, none). Using the traditional Oracle syntax, joining the tables in the following order:


  my_other_other_table,
  my_other_table,
  my_tablec

we eliminate only the join involving my_other_table:


BING @ orclpdb1 > 
BING @ orclpdb1 > select
  2  	 count(c.my_varch)
  3  from
  4  	 my_other_other_table g,
  5  	 my_other_table      p,
  6  	 my_table	c
  7  where
  8  	 c.my_num between 200 and 215
  9  and p.my_other_id	 = c.my_other_id
 10  and p.my_other_other_id = c.my_other_other_id
 11  and g.my_other_other_id   = p.my_other_other_id
 12  ;

COUNT(C.MY_VARCH)
-----------------
               80

BING @ orclpdb1 > 
BING @ orclpdb1 > select * from table(dbms_xplan.display_cursor(null,null,'outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fmu4cu5mqprtt, child number 0
-------------------------------------
select     count(c.my_varch) from     my_other_other_table g,
my_other_table      p,     my_table       c where     c.my_num between
200 and 215 and p.my_other_id   = c.my_other_id and p.my_other_other_id
= c.my_other_other_id and g.my_other_other_id   = p.my_other_other_id

Plan hash value: 2096404014

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |       |       |    55 (100)|          |
|   1 |  SORT AGGREGATE     |                   |     1 |    23 |            |          |
|   2 |   NESTED LOOPS      |                   |    85 |  1955 |    55   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| MY_TABLE          |    85 |  1615 |    55   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| MY_OTHER_OTHER_PK |     1 |     4 |     0   (0)|          |
-----------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$A43D1678")
      ELIMINATE_JOIN(@"SEL$1" "P"@"SEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$A43D1678" "C"@"SEL$1")
      INDEX(@"SEL$A43D1678" "G"@"SEL$1" ("MY_OTHER_OTHER_TABLE"."MY_OTHER_OTHER_ID"))
      LEADING(@"SEL$A43D1678" "C"@"SEL$1" "G"@"SEL$1")
      USE_NL(@"SEL$A43D1678" "G"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   3 - filter(("C"."MY_NUM"=200))
   4 - access("G"."MY_OTHER_OTHER_ID"="C"."MY_OTHER_OTHER_ID")


44 rows selected.

The highlighted text in the outline proves only one join was eliminated, even though the data would allow the optimizer to eliminate both joins. The table order makes the difference; with this order the join between my_table and my_other_table can be eliminated but the join between that row source and my_other_other_table cannot be. Changing the order of the FROM clause to:


  my_table,
  my_other_table,
  my_other_other_table

provides the optimizer with the ‘correct’ order to eliminate both joins:


BING @ orclpdb1 > 
BING @ orclpdb1 > select
  2  	 count(c.my_varch)
  3  from
  4  	 my_table	c,
  5  	 my_other_table      p,
  6  	 my_other_other_table g
  7  where
  8  	 c.my_num between 200 and 215
  9  and p.my_other_id	 = c.my_other_id
 10  and p.my_other_other_id = c.my_other_other_id
 11  and g.my_other_other_id   = p.my_other_other_id
 12  ;

COUNT(C.MY_VARCH)
-----------------
               80

BING @ orclpdb1 > 
BING @ orclpdb1 > select * from table(dbms_xplan.display_cursor(null,null,'outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dur2a6u4k0dw7, child number 0
-------------------------------------
select     count(c.my_varch) from     my_table       c,
my_other_table      p,     my_other_other_table g where     c.my_num
between 200 and 215 and p.my_other_id   = c.my_other_id and
p.my_other_other_id = c.my_other_other_id and g.my_other_other_id   =
p.my_other_other_id

Plan hash value: 3996063390

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |    55 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |    15 |            |          |
|*  2 |   TABLE ACCESS FULL| MY_TABLE |    85 |  1275 |    55   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$EE3A0715")
      ELIMINATE_JOIN(@"SEL$1" "P"@"SEL$1")
      ELIMINATE_JOIN(@"SEL$1" "G"@"SEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$EE3A0715" "C"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   2 - filter(("C"."MY_NUM"=200))


40 rows selected.

With this order the optimizer can eliminate both joins it joine my_other_other_table to my_other_table, reducing that join to a single-table query against my_other_table, and that allows for the second join to be eliminated (as in the first query) leaving only my_table as the row source.

Since Oracle allows for using either the ‘native’ Oracle syntax or the ANSI syntax the example can be rewritten to conform to the ANSI syntax. Using the table order from the first Oracle-syntax query the optimizer has no problem eliminating both joins:


BING @ orclpdb1 > 
BING @ orclpdb1 > select
  2  	 count(c.my_varch)
  3  from
  4  	 my_other_other_table g
  5  join
  6  	 my_other_table      p
  7  on  p.my_other_other_id = g.my_other_other_id
  8  join
  9  	 my_table	c
 10  on  c.my_other_id = p.my_other_id
 11  and c.my_other_other_id = p.my_other_other_id
 12  where
 13  	 c.my_num between 200 and 215
 14  ;

COUNT(C.MY_VARCH)
-----------------
               80

BING @ orclpdb1 > 
BING @ orclpdb1 > select * from table(dbms_xplan.display_cursor(null,null,'outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1avs3dy4zyfyf, child number 0
-------------------------------------
select     count(c.my_varch) from     my_other_other_table g join
my_other_table      p on  p.my_other_other_id = g.my_other_other_id
join     my_table       c on  c.my_other_id = p.my_other_id and
c.my_other_other_id = p.my_other_other_id where     c.my_num between
200 and 215

Plan hash value: 3996063390

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |    55 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |    15 |            |          |
|*  2 |   TABLE ACCESS FULL| MY_TABLE |    85 |  1275 |    55   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$8E5E10D6")
      ELIMINATE_JOIN(@"SEL$9DC5130D" "P"@"SEL$1")
      OUTLINE(@"SEL$9DC5130D")
      MERGE(@"SEL$E7C364C4" >"SEL$3")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$E7C364C4")
      MERGE(@"SEL$1C2B93D4" >"SEL$2")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1C2B93D4")
      ELIMINATE_JOIN(@"SEL$1" "G"@"SEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$8E5E10D6" "C"@"SEL$2")
      END_OUTLINE_DATA
  */

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

   2 - filter(("C"."MY_NUM"=200))


47 rows selected.

In yet another interesting turn of events if the join order is changed to that of the “successful” query writtem using Oracle syntax only the join involving my_other_table is eliminated:


BING @ orclpdb1 > 
BING @ orclpdb1 > select
  2  	 count(c.my_varch)
  3  from
  4  	 my_table	c
  5  join
  6  	 my_other_table      p
  7  on      p.my_other_id   = c.my_other_id
  8  and p.my_other_other_id = c.my_other_other_id
  9  join
 10  	 my_other_other_table g
 11  on  g.my_other_other_id = p.my_other_other_id
 12  where
 13  	 c.my_num between 200 and 215
 14  ;

COUNT(C.MY_VARCH)
-----------------
               80

BING @ orclpdb1 > 
BING @ orclpdb1 > select * from table(dbms_xplan.display_cursor(null,null,'outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3jqqzwwsy9t6r, child number 0
-------------------------------------
select     count(c.my_varch) from     my_table       c join
my_other_table      p on      p.my_other_id   = c.my_other_id and
p.my_other_other_id = c.my_other_other_id join     my_other_other_table
g on  g.my_other_other_id = p.my_other_other_id where     c.my_num
between 200 and 215

Plan hash value: 2096404014

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |       |       |    55 (100)|          |
|   1 |  SORT AGGREGATE     |                   |     1 |    23 |            |          |
|   2 |   NESTED LOOPS      |                   |    85 |  1955 |    55   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| MY_TABLE          |    85 |  1615 |    55   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| MY_OTHER_OTHER_PK |     1 |     4 |     0   (0)|          |
-----------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$935DEA34")
      MERGE(@"SEL$AAEC2A2A" >"SEL$3")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$AAEC2A2A")
      MERGE(@"SEL$A43D1678" >"SEL$2")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$A43D1678")
      ELIMINATE_JOIN(@"SEL$1" "P"@"SEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$935DEA34" "C"@"SEL$1")
      INDEX(@"SEL$935DEA34" "G"@"SEL$2" ("MY_OTHER_OTHER_TABLE"."MY_OTHER_OTHER_ID"))
      LEADING(@"SEL$935DEA34" "C"@"SEL$1" "G"@"SEL$2")
      USE_NL(@"SEL$935DEA34" "G"@"SEL$2")
      END_OUTLINE_DATA
  */

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

   3 - filter(("C"."MY_NUM"=200))
   4 - access("G"."MY_OTHER_OTHER_ID"="C"."MY_OTHER_OTHER_ID")


51 rows selected.

It is worth noting that when Oracle processes an ANSI syntax join it first converts it to an equivalent Oracle-syntax join then proceeeds with the optimization/join elimination. With the first ANSI example the re-write essentially produces a query of the form


	select [] from (select [] from my_other_other_table join my_other_table) join my_table

which allows for elimination of the my_other_other_table join, leaving the my_other_table, my_table join which, given the data set, can also be eliminated leaving a simple query againt my_table. The re-write of tne first ANSI join produces basically the same query as the second Oracle-syntax query in the example. With the other ANSI query the resulting re-write is of the form:


	select [] from (select [] from my_table join my_other_table) join my_other_other_table

That essentially matches the first Oracle-syntax query in the example which provides the optimizer with only enough information to eliminate the first join.

It’s a good idea to check execution plans for queries utilizing multiple joins on primary-key columns; it’s possible that simply changing the order of the tables could change the execution plan, as illustrated here. It’s also worth noting that ANSI-syntax queries can behave (as far as the optimizer is concerned) in opposite ways because of the way Oracle re-writes them.

Which should be expected.

February 6, 2019

“It’s Nice To Share…”

Filed under: General — dfitzjarrell @ 11:04

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

In a valiant attempt to let the adminstrator affect performance Oracle has provided various settings in the spfile to enable or disable features that could, possibly, make things go faster. Long on the list of bloggers has been cursor_sharing, the parameter used to tell Oracle how to treat query text. By default it’s set to EXACT, which means Oracle takes the query as-is and executes it. This can, however, clutter the shared SQL area with what appear to be multiple copies of the same query. differing only in the literal values used. One setting for cursor_sharing, FORCE, causes Oracle to treat every query as though it’s using bind variables, or so DBAs were lead to believe. As George Gershwin so eloquently put it — “It ain’t necessarily so.” Let’s look at conditions that can throw a curve ball in this bind-variable circus.

If a query contains nothing but SQL text and literal values then cursor_sharing = FORCE does what DBAs have expected for years — the literals are replaced with bind variables and, using bind variable peeking, the query is parsed and executed. This allows for almost unlimited reuse of a cursor, since the cursor is generated with bind variables and Oracle then uses the shared cursor to execute the query, using the system-generated bind variables to hold the literal values the original query contained. The problem arises when a query, tnat once used only literal values, is slightly rewritten to include a user-defined bind variable. This ‘derails’ the system-generated bind variable train and that good plan is no longer the default plan since the optimizer, having a new bind variable to contend with, reparses and generates a new, not necessarily better, plan.

Thanks to Jonathan Lewis we have an example to illustrate this. Let’s use the data set he’s so generously provided:


rem
rem     Script:         cursor_sharing_limit.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jun 2018
rem     Purpose:
rem
rem     Last tested
rem             18.1.0.0
rem             12.2.0.1
rem             12.1.0.2
rem

create table t1
as
select
        rownum            n1,
        rownum            n2,
        lpad(rownum,10)   small_vc,
        rpad('x',100,'x') padding
from dual
connect by
        level <= 1e4
;

The next step is to execute queries that, supposedly, should undergo bind variable substitution at the hands of cursor_sharing=FORCE, but don’t. Jonathan has also generously provided that code:


alter system flush shared_pool;
alter session set cursor_sharing=force;

declare
        m_ct number;
        m_n1 number := 20;
begin
        execute immediate
                'select /*+ trace this */ count(*) from t1 where n2 = 15 and n1 = :b1'
                into m_ct using m_n1;
        dbms_output.put_line(m_ct);

        execute immediate
                'select /*+ trace this too */ count(*) from t1 where n1 = 15 and n2 = 15'
                into m_ct;
        dbms_output.put_line(m_ct);
end;
/

alter session set cursor_sharing=exact;

The ‘hints’ (which are really nothing more than comments) make it fairly easy to find and return the query text Oracle generates when those queries are executed. Putting this all together into a full example produces:


SQL> set linesize 300 trimspool on pagesize 50
SQL>
SQL> rem
SQL> rem        Script:      cursor_sharing_limit.sql
SQL> rem        Author:      Jonathan Lewis
SQL> rem        Dated:       Jun 2018
SQL> rem        Purpose:
SQL> rem
SQL> rem        Last tested
SQL> rem             18.1.0.0
SQL> rem             12.2.0.1
SQL> rem             12.1.0.2
SQL> rem
SQL>
SQL> create table t1
  2  as
  3  select
  4          rownum            n1,
  5          rownum            n2,
  6          lpad(rownum,10)   small_vc,
  7          rpad('x',100,'x') padding
  8  from dual
  9  connect by
 10          level  comment to avoid WordPress format issue
 11  ;

Table created.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing=force;

Session altered.

SQL>
SQL> declare
  2          m_ct number;
  3          m_n1 number := 20;
  4  begin
  5          execute immediate
  6                  'select /*+ trace this */ count(*) from t1 where n2 = 15 and n1 = :b1'
  7                  into m_ct using m_n1;
  8          dbms_output.put_line(m_ct);
  9
 10          execute immediate
 11                  'select /*+ trace this too */ count(*) from t1 where n1 = 15 and n2 = 15'
 12                  into m_ct;
 13          dbms_output.put_line(m_ct);
 14  end;
 15  /
0
1

PL/SQL procedure successfully completed.

SQL>
SQL> alter session set cursor_sharing=exact;

Session altered.

SQL>
SQL> select     sql_id, parse_calls, executions, rows_processed, sql_text
  2  from       v$sql
  3  where      sql_text like 'select%trace this%'
  4  and        sql_text not like '%v$sql%'
  5  ;

SQL_ID        PARSE_CALLS EXECUTIONS ROWS_PROCESSED
------------- ----------- ---------- --------------
SQL_TEXT
-----------------------------------------------------------------------------------------
cbu4s78h5pfj5           1          1              1
select /*+ trace this too */ count(*) from t1 where n1 = :"SYS_B_0" and n2 = :"SYS_B_1"

cru67sufptx8x           1          1              1
select /*+ trace this */ count(*) from t1 where n2 = 15 and n1 = :b1


SQL>

Notice that the first query in the script, with a combination of a literal value and a user-defined bind variable, underwent no substitution with respect to the literal value. If straight SQL is used, through SQL*plus, this is what happens:


SQL> variable b1 number
SQL>
SQL> begin
  2          :b1 := 15;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ SQL*Plus session */ count(*) from t1 where n2 = 15 and n1 = :b1;

  COUNT(*)
----------
         1

SQL>
SQL> select     sql_id, parse_calls, executions, rows_processed, sql_text
  2  from       v$sql
  3  where      sql_text like 'select%Plus session%'
  4  and        sql_text not like '%v$sql%'
  5  ;

SQL_ID        PARSE_CALLS EXECUTIONS ROWS_PROCESSED
------------- ----------- ---------- --------------
SQL_TEXT
--------------------------------------------------------------------------------------------------
gq2qy2a9yuta7           1          1              1
select /*+ SQL*Plus session */ count(*) from t1 where n2 = :"SYS_B_0" and n1 = :b1


SQL>

Executing this through SQL*Plus directly, without the context switch of ‘execute immediate’ from a PL/SQL block, changes the behavior of the optimizer so that Oracle provides the expected results. It’s interesting, to say the least.

The expected doesn’t always happen with SQL statements, and that may be due to how those statements reach the optimizer. Knowing when, and when not, to expect behavior may make explaining things to users and developers a bit less stressful. As Norton Juster put it in “The Phantom Tollbooth”: “Expect everything, I always say, and the unexpected never happens.” Which is good advice for a DBA.

Even when you take a different road.

Next Page »

Create a free website or blog at WordPress.com.