how about efficiency with "select * from table where id in (17,100,200..)" condition

[silentyears]

Hi,
As subject, i want to know if i use sql statement "select * from table where id in (17,100,200...)" to select the records from table on Oracle database, how about the efficiency? Is there any idea to get the same purpose with better select speed? (Qt version is 5.1.1)

[Lesiok]

This have nothing to Qt. This is the problem of the structure of the database.

[anda_skoa]

Wouldn't it be better to ask the on an Oracle forum?

The people there will know best how to write queries in the Oracle dialect of SQL.

Cheers,
_

[ChrisW67]

As a former Oracle DBA I can offer the following advice:

Know which queries are actually a problem before you optimise any of them.
Only select the columns you need a value from. "Select *" is counter to both efficiency and maintainability.
Run the query in SQL*Plus and look at the "EXPLAIN PLAN ...". Oracle's query engine has a range of ways to handle the IN() based on heuristics and the specific query. Often it results in exactly the same query as multiple ORed equality tests, but sometimes it's smarter than you think.
Make sure the ID column is indexed (alone or the first column in a compound index) if the table is of substantial size.

The IN() construct is limited to 1000 entries. If you are doing that then you are probably should be asking yourself why.