Re: Trying to avoid using the IN Clause in SQL statment

Have you tried to use the FIRST_ROWS hint? 

If your db is set to CBO and the stocknumber column is indexed 
then Oracle will decide by one INLIST ITERATOR operation over one INDEX
RANGE SCAN operation, instead of a CONCATENATION over multiple INDEX
UNIQUE SCAN operations.

Depending on the number of values in the IN list you may get better
performance or not. 

Roque Daudt

--- [email protected] wrote:
> I have an ASP page that allows a user to submit a text file that is 
> composed of one column of say... inventory stock numbers. I grab the
> file 
> and retrieve the numbers. I then want to query several tables in an
> Oracle 
> 8.1.7 database for information for these values. I currently have
> designed 
> the SQL like this.
> 
>    "SELECT sometable.something, othertable.otherthing FROM sometable,
> 
> othertable WHERE stockNumber IN ('VAL1','VAL2'.....)"
> 
> I realize this is inefficient for a large qty of "VALs" so I have
> limited 
> the size of the file the user can submit however this is not a
> long-term 
> solution. 
> 
>   I am looking for some advice on maybe creating a temporary table
> and 
> inserting the values into the table where then I can run queries
> against 
> it efficiently. I know I could create table and "INSERT INTO ....
> VALUES
> ( ... )”, but is there an even better approach. Suggestions would be 
> greatly appreciated.
> 
> 
>   
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or 
> to unsubscribe send a blank email to
%%email.unsub%%.


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].