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

Hi

If this code is in a PL/SQL block you can make it quicker.

Instead of using "NOT IN" with a sub-query theres something better. Remember that sub-queries cause in general a BIG performance loss simply because they are executed (and re-executed needlessly) for every row in the outer query. That is for every row in the message table, this query runs again and again etc. Now you can see that this is not necessary since (assumption) the sub-query should typically be querying some "look-up" type table which should be static for the entire execution of the outer query.

So here's the idea. If you can guarantee (do a simple check) that the total sum of all the characters + 1 character for a separating charater returned by all the rows of the sub-query is less that 2000 chars, then here's a neat trick.

Pre-construct the rows that will be returned by the sub-query and store it as string.
Call the string my_list. For example

DECLARE my_list VARCHAR2(2000);

FOR List in (SELECT TO_CHAR(c.cust_no) string

                    FROM customer c
                   ) LOOP

         my_list:= List.string||'~'||my_list;

• note the '~' is a separator

END LOOP; Then replace the sub-query by :
INSTR(my_list,m.cust_no)=0

So now you have :

SELECT m.cust_no
FROM message m
WHERE INSTR(my_list,m.cust_no)=0;

This is WAY quicker.

Of course if the rows returned by sub-query is large (plus you need some separator)
and the sum of this exceeds 2000 chars - then you will have to create multiple INSTR operations with a mutually exlusive part of the sub-query result set in each.

Good luck and hope it helps.

Kugendran Naidoo
Independant Oracle DBA

> Dr. Wei Ho wrote in message <371AF6C9.41C67EA6_at_etl.ericsson.se>...
> >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);
>
Received on Tue Apr 20 1999 - 13:25:47 CEST