Re: How can I make it faster? Help needed.

A copy of this was sent to "Dr. Wei Ho" <wei.he_at_etl.ericsson.se> (if that email address didn't require changing) On Mon, 19 Apr 1999 10:26:34 +0100, you wrote:

>Hi,
>
>Can anybody out there help me to make the following query faster.
>
>Both tables, MESSAGE and CUSTOMER, in the query have about 13,000
>records. In both tables, the fields CUST_NO are indexed.
>
>SELECT m.cust_no
> FROM message m
> WHERE m.cust_no
>NOT IN (SELECT c.cust_no FROM customer c);
>

not in typically won't use indexes on the subquery and usually results in a full scan of the subquery table for each row in the outer query. This query will always full scan message but we can make it use an index on customer(cust_no) if one exists by coding:

select m.cust_no
  from message m
 where NOT EXISTS ( select null

                      from customer c
                     where c.cust_no = m.cust_no )

>Thanks
>
>Wei
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation