Re: Forms Question : How to avoid IN operator in the WHERE CLAUSE

Use where EXISTS instead of IN opeartor.For example consider a query below select emp_id, emp_name from employee where dept_code in (select dept_code from department where dept_code between 20 and 100)

This can be converted to use EXISTS as below select emp_id,emp_name from employee a where exists (select dept_code from department b where a.dept_code = b.dept_code and b.dept_code between 20 and 100)
This is an example and may not be exactly true (Use something like this).EXISTS can be used in all queries where IN and Not in operator is there. And no doubt it improves performance by more than 100% subject to proper use of indexed columns an sometimes using HINTS TEXT.

Hope this helps.
Ashok

Madhu Cherukuri wrote:

> Hi,
>
> Does any body has some ideas about this question?
>
> I have a where clause which uses a sub-query and
>
> IN operator to perform the main query.
>
> Thsi is causing Full Table scan and I want to
>
> avoid it. Is there any workaround for this in the
>
> Forms?(Forms 4.5)
>
> Where clause is like this
>
> where col1 IN (select col2 from Table2 where col2 like 'XYZ')
>
> Col1 is from another table and has a relationship with col2 of Table2
>
> Thanks in advance
>
> Madhu Cherukuri
>
> -**** Posted from Supernews, Discussions Start Here(tm) ****-
> http://www.supernews.com/ - Host to the the World's Discussions & Usenet
Received on Thu Nov 19 1998 - 15:42:37 CET