Home » SQL & PL/SQL » SQL & PL/SQL » Optimizing Select with multiple ids in where clause
Optimizing Select with multiple ids in where clause [message #24710] Fri, 28 February 2003 15:54 Go to next message
Tobias Teite Nilsson
Messages: 3
Registered: February 2003
Junior Member
Hi,

What is the best way of doing a bulk select using JDBC to retrieve a Resultset from a table with multiple values of the same column? Base query is 'select * from table where id in (?, ?, .. n). Table will have around 200,000 lines and n may be up to 500.

I have a couple of ideas to get around this but would like to know if there is a better way to do this

1. To use an in-clause with as many ids as there are. I heard there are issues with this because Oracle uses a full table scan when n > 10, can this be configured to 'force' Oracle to use index?

2. Use a union statement i.e
* from table where id = ? union * from table where id = ? union ...

It would be awesome if there was a way to do batch select but I have not found a way to do this in JDBC?

I appreciate any help or pointers,

thanks, Tobias
Re: Optimizing Select with multiple ids in where clause [message #24711 is a reply to message #24710] Fri, 28 February 2003 16:28 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
If you don't have a fixed amount of entries in the IN list, then you are building the statement dynamically - which is going to kill your shared pool (all these dynamic statements, all with different literal values).

Friendly suggestion: don't do that. Instead, use object types and collections so there is one fixed statement with a bind variable parameter containing the comma-delimited list of values. Oracle can parse this statement once and execute it over and over. This approach works extremely well - I use it in many different systems.

See here for more details:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:110612348061

Also, there is no "conversion to FTS once over 10 entries in an IN list." Oracle will gladly use an index with long IN lists if the optimizer believes that is the optimal execution plan. The UNION approach is also a bad idea for many of the same reasons stated above - it will also be slower.

Hope this helps - feel free to ask followups...
Re: Optimizing Select with multiple ids in where clause [message #24724 is a reply to message #24710] Mon, 03 March 2003 09:43 Go to previous messageGo to next message
Tobias Teite Nilsson
Messages: 3
Registered: February 2003
Junior Member
Thanks for your response, this helps!

I have a couple of follow-up questions (or maybe clarifications). Just want to make sure I understand what you mean with using object types and collections so there is one fixed statement with a bind variable parameter containing the comma-delimited list of values.

So instead of building the select statement dynamically, fix the statement like select * from table where id in (?, ?, .. ?), say the variable list is 20. Then call this prepared statement the number of times needed to execute all the variables (the statement would not need to be re-parsed). I think this would solve part of the problem, but we would have to send the whole statement from the app server to the network to the DB server each time. Id there a way to get around that, i.e only send the Collection of variables to the DB and execute the statement.

Oracle documentation says that it is possible to use hints to 'force' Oracle to use the indexes for select statements with big in-clauses. Is this the way to set it up or does Oracle optimizer figure out what gives the best performance?

I also checked out the 'asktom' site and I think the solution there deals with PL/SQL. In this environment using the DB Layer od the architecture is completely Java based with JDBC and using PL/SQL would be the last option.

thanks,
Tobias
Re: Optimizing Select with multiple ids in where clause [message #24729 is a reply to message #24724] Mon, 03 March 2003 11:26 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I don't understand why you would consider PL/SQL a "last option". PL/SQL is as tightly coupled to the database as you can get - it is the best tool for database code.

Many people would consider a well-done Java/JDBC app as one that does not contain a single DML statement of any kind (SELECT or otherwise). Everything would be handled by stored procedures and result sets.

That said, if your select statement is going to reside on the Java side, you would need at least one PL/SQL function to accomplish the object type/collection approach - the function that converts the comma-delimited string into a collection.

Here is an example - note how there is just one statement with one bind variable. It is just the value of the bind variable that changes with each execution. The statement itself is not dynamically built and does not have a fixed set of "slots."

sql>create or replace type tNumberTable is table of number;
  2  /
 
Type created.
 
sql>create or replace function f_get_number_table_from_string(
  2      p_string     in  varchar2,
  3      p_delimiter  in  varchar2 default ',')
  4      return tNumberTable
  5  is
  6    v_string  long := p_string || p_delimiter;
  7    v_pos     pls_integer;
  8    v_data    tNumberTable := tNumberTable();
  9  begin
 10    loop
 11      v_pos := instr(v_string, p_delimiter);
 12      exit when (nvl(v_pos, 0) = 0);
 13      v_data.extend;
 14      v_data(v_data.count) := trim(substr(v_string, 1, v_pos - 1));
 15      v_string := substr(v_string, v_pos + 1);
 16    end loop;
 17    return (v_data);
 18  end f_get_number_table_from_string;
 19  /
 
Function created.
 
sql>var list varchar2(256)
sql>exec :list := '7369,7499,7521'
 
PL/SQL procedure successfully completed.
 
sql>select empno, ename
  2    from emp
  3   where empno in (select *
  4                     from table(cast(f_get_number_table_from_string(:list) as tNumberTable)));
 
    EMPNO ENAME
--------- ----------
     7369 SMITH
     7499 ALLEN
     7521 WARD
 
3 rows selected.
Re: Optimizing Select with multiple ids in where clause [message #24742 is a reply to message #24724] Tue, 04 March 2003 08:31 Go to previous message
Tobias Teite Nilsson
Messages: 3
Registered: February 2003
Junior Member
Thanks again Todd!

Thanks for the explaination and the example. This will help much.

In this project it is considered to be the 'last' option because this is the architecture definition due to a loosely coupled application from the database. However in the name of performance this may have to be changed.
Previous Topic: any quick suggestions on picking data
Next Topic: Logon trigger: how to obtain the current session id?
Goto Forum:
  


Current Time: Wed Jul 30 10:58:16 CDT 2025