Hi,

I need to speed up a query that takes upwards of 2 minutes to execute on a
table that currently has 14000 records, but will soon have 2+ million
records. If it takes 2 minutes to execute on 14000 records, I don't want to
see what it will be like on 2 million records! I think it is taking long
because of a "not in" clause, and I know I should try to use "not exists"
instead, but my first attempts at doing that were incorrect.

Here is the basic table structure:
MY_TABLE_ID    ORDER_ITEM_ID    RETURN_ID   LINKING_ID
1                     1
2                     1                        90
3                     1                        90
4                     1
5                     1
6                     1
7                     1                        90                    1

The LINKING_ID is a foreign key to MY_TABLE_ID; can be NULL.

Here is my SQL:
select * from MY_TABLE
where ORDER_ITEM_ID = 1
and RETURN_ID is null
and MY_TABLE_ID not in
  (select distinct nvl(LINKING_ID,0)
   from MY_TABLE where ORDER_ITEM_ID = 1
   and RETURN_ID is not null)

I want those records for a particular item where the RETURN_ID is null and
there are no LINKING_ID records pointing to it. For the above sample
records, the result should be records 4,5 and 6.

Any help is appreciated.

Thanks,
Natalie

Ok, lets try to speed it up!

The syntax for the 'not exists' thing  should be

 select * from MY_TABLE Table1
 where ORDER_ITEM_ID = 1
and RETURN_ID is null
And not exists
(  select  1 from my_table table2
   where table2.linking_id=table1.order_item_id   -- or =1, if it is
constant)
   and return_id is not null)

you may need indexes on order_item_id, return_id and linking_id.

Did you fill the statistics of the table with analyze table?
(after creating the indexes, do the following
analyze table MY_TABLE estimate statistics for all indexed columns;
analyze table MY_TABLE estimate statistics;

What does explain plan say?

Hope, it helped a bit,

bye Uli

Thanks so much. That did speed it up a lot.
Natalie

Or use a join:

select t1.* from my_table t1, my_table t2
where t1.order_item_id = 1
and return_id is null
and t1.my_table_id = t2.linking_id (+)
and t2.linking_id is null;

...and, make sure that there are indexes on my_table_id and linking_id...

----------------------------------------------------
Corey Lawson
claw...@bogusucsd.edu
(remove the 'bogus', as it's my feeble attempt
 to defeat spammer address suckers)