2

I maintain an application where I am trying to optimize an Oracle SQL query wherein multiple IN clauses are used. This query is now a blocker as it hogs nearly 3 minutes of execution time and affects application performance severely.The query is called from Java code(JDBC) and looks like this : 

Select disctinct col1,col2,col3,.. colN from Table1
where 1=1 and not(col1 in (idsetone1,idsetone2,... idsetoneN)) or
(col1 in(idsettwo1,idsettwo2,...idsettwoN))....
(col1 in(idsetN1,idsetN2,...idsetNN))

The ID sets are retrieved from a different schema and therefore a JOIN between column1 of table 1 and ID sets is not possible. ID sets have grown over time with use of the application and currently they number more than 10,000 records.

How can I start with optimizing this query ?

user2768412
  • 21
  • 1
  • Why are you using multiple `in` clauses? They are all on the same column. Combine the lists into a single list. – Gordon Linoff Jan 12 '15 at 12:47
  • If you use `NOT IN` and `OR` will take time, hence try to avoid as Gordon Linoff said – Exhausted Jan 12 '15 at 12:49
  • 2
    @GordonLinoff Oracle accepts up to 1000 elements in list. – Florin Ghita Jan 12 '15 at 12:52
  • 5
    What do you really mean by "a different schema"? Obviously it *is* possible to join tables from different schemas: `from schema1.table1 join schema2.tabl2...` – Tony Andrews Jan 12 '15 at 12:52
  • https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061 – OldProgrammer Jan 12 '15 at 12:53
  • additionally `1=1` condition is handy for creating dynamic sql, but might confuse the optimizier. Had it at least one time, that indexes weren't used properly... – evilive Jan 12 '15 at 12:58
  • Are you selecting the IDs from one JDBC connection and using them in another, and the two DBs cant be connected together via a link? If so have you looked at using an array to pass all of the values in one go? ([This sort of thing](http://stackoverflow.com/a/26746950/266304)). – Alex Poole Jan 12 '15 at 15:47

2 Answers2

2

I really doupt about "The ID sets are retrieved from a different schema and therefore a JOIN between column1 of table 1 and ID sets is not possible." Of course you can join the tables, provided you got select privileges on it.

Anyway, let's assume it is not possible due to whatever reason. One solution could be to insert all entries first into a Nested Table and the use this one:

CREATE OR REPLACE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER;

Select disctinct col1,col2,col3,.. colN from Table1
where 1=1 
   and not (col1 NOT MEMBER OF (NUMBER_TABLE_TYPE(idsetone1,idsetone2,... idsetoneN)) 
      OR
      (col1 MEMBER OF NUMBER_TABLE_TYPE(idsettwo1,idsettwo2,...idsettwoN))

Regarding the max. number of elements Oracle Documentation says: Because a nested table does not have a declared size, you can put as many elements in the constructor as necessary.

I don't know how serious you can take this statement.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • 1
    You should prepare the Statement with a Bind-variable MEMBER OF :1 and directly construct the nested Table with JDBC and put it in there - than it will be streamed to the database and you don't have to build/parse gigantig SELECT-Strings. It will be blazing fast! – Falco Jan 12 '15 at 15:27
  • FYI - you misspelled `DISTINCT`, but I see that's from the OP – David Faber Jan 12 '15 at 15:36
0

You should put all the items into one temporary table and to an explicit join:

Select your cols
from Table1
left join table_with_items 
 on table_with_items.id = Table1.col1
where table_with_items.id is null;

Also that distinct suggest a problem in your business logic or in the architecture of application. Why do you have duplicate ids? You should get rid of that distinct.

Florin Ghita
  • 17,525
  • 6
  • 57
  • 76